Junior
Hirdetési kampányok felhasználói aktivitásának elemzése A vállalat nyilvántartást vezet a hirdetési kampányokkal kapcsolatos eseményekről. Két táblát biztosítanak: • campaigns — a hirdetési kampányok listája azonosítóikkal és neveikkel; • events — felhasználói események kampányonként, az esemény típusával (pl. 'click' (kattintás)) és időpontjával. Szükséges minden kampányhoz jelentést készíteni a következő mutatókkal: • Események összes száma. • Az eseményeket végrehajtó egyedi felhasználók száma. • Az első és utolsó esemény időpontja kampányonként. • A kampány rangsorolása a teljes eseményszám szerint csökkenő sorrendben. A rangsor egy szám, amelyet minden sorhoz hozzárendelnek az eredményhalmazban az adatok sorrendje alapján. Ha két vagy több sor azonos értéket tartalmaz, ugyanazt a rangot kapják, de a következő rang kihagyásra kerül. Csak azok a kampányok szerepeljenek a jelentésben, amelyeknek volt eseményük: a kampányok esemény nélkül nem kerülnek figyelembevételre. A végső jelentést először a kampány rangsora szerint növekvő sorrendben kell rendezni, majd a campaign_name szerint ábécé sorrendben. Bemeneti formátum • campaign_name (string) — a hirdetési kampány neve • start_date (timestamp) — a kampány kezdő dátuma és ideje • end_date (timestamp) — a kampány befejező dátuma és ideje Események táblázat: • event_id (int) — az esemény egyedi azonosítója • user_id (int) — az eseményt végrehajtó felhasználó egyedi azonosítója • campaign_id (int) — a kampány egyedi azonosítója • event_type (string) — az esemény típusa, pl. 'click' vagy 'conversion' • event_time (timestamp) — az esemény dátuma és időpontja Az adatok nem tartalmaznak hiányzó vagy hibás értékeket. Kimeneti formátum A lekérdezésnek egy olyan táblát kell visszaadnia, amely a következő mezőket tartalmazza ebben a sorrendben: • campaign_name (string) — a hirdetési kampány neve • total_events (int) — a kampánnyal kapcsolatos összes esemény száma • unique_users (int) — az eseményeket végrehajtó egyedi felhasználók száma • first_event_time (timestamp) — a kampány első eseményének dátuma és időpontja • last_event_time (timestamp) — a kampány utolsó eseményének dátuma és időpontja • campaign_rank (int) — a kampány rangsora a teljes eseményszám szerint csökkenő sorrendben A dataokat a kampány rangsora szerint növekvő sorrendbe, majd a campaign_name szerint ábécé sorrendbe kell rendezni.
sobes.tech MI
Válasz az MI-től
sql WITH event_stats AS ( SELECT c.campaign_name, e.campaign_id, COUNT(*) AS total_events, COUNT(DISTINCT e.user_id) AS unique_users, MIN(e.event_time) AS first_event_time, MAX(e.event_time) AS last_event_time FROM campaigns c JOIN events e ON c.campaign_id = e.campaign_id GROUP BY c.campaign_name, e.campaign_id ), ranked AS ( SELECT campaign_name, total_events, unique_users, first_event_time, last_event_time, RANK() OVER (ORDER BY total_events DESC) AS campaign_rank FROM event_stats ) SELECT campaign_name, total_events, unique_users, first_event_time, last_event_time, campaign_rank FROM ranked ORDER BY campaign_rank ASC, campaign_name ASC;
Объяснение:
- Сначала объединяем таблицы campaigns и events по campaign_id, чтобы получить события с названиями кампаний.
- Группируем по кампании, считаем общее количество событий, уникальных пользователей, минимальное и максимальное время события.
- Используем оконную функцию RANK() для присвоения ранга по убыванию total_events.
- В итоговом запросе сортируем по возрастанию ранга и по алфавиту названия кампании.
- Кампании без событий не попадут в результат, так как мы делаем внутреннее соединение с events.
Таким образом, получаем отчет с нужными метриками и ранжированием.