Junior
Analýza aktivity používateľov v reklamných kampaniach Spoločnosť vedie evidenciu udalostí súvisiacich s reklamnými kampaňami. Vstupné tabuľky sú: • campaigns — zoznam reklamných kampaní s ich identifikátormi a názvami; • events — udalosti používateľov podľa kampaní s informáciami o type udalosti (napríklad 'click' (klik na reklamu)) a čase. Je potrebné vytvoriť správu pre každú reklamnú kampaň s nasledujúcimi ukazovateľmi: • Celkový počet udalostí. • Počet jedinečných používateľov, ktorí vykonali udalosti. • Čas prvého a posledného udalosti podľa kampane. • Poradie kampane podľa zostupného počtu celkových udalostí. Riadok — číslo, ktoré sa priraďuje každej riadku v výslednom súbore na základe zadaného poradia údajov. Ak majú dve alebo viac riadkov rovnakú hodnotu, dostanú rovnaké poradie, ale nasledujúce poradie sa preskočí. Do správy sa zahrnú iba kampane, ktoré mali udalosti: kampane bez udalostí sa nezohľadňujú. Konečný prehľad by mal byť najskôr zoradený podľa poradia kampane vzostupne, a potom podľa campaign_name v abecednom poradí. Formát vstupu • campaign_name (string) — názov reklamnej kampane • start_date (timestamp) — dátum a čas začiatku reklamnej kampane • end_date (timestamp) — dátum a čas ukončenia reklamnej kampane Tabuľka events: • event_id (int) — jedinečný identifikátor udalosti • user_id (int) — jedinečný identifikátor používateľa, ktorý vykonal udalosť • campaign_id (int) — jedinečný identifikátor reklamnej kampane • event_type (string) — typ udalosti, napríklad 'click' (klik na reklamu) alebo 'conversion' (konverzia — úspešná akcia, napríklad nákup) • event_time (timestamp) — dátum a čas vykonania udalosti Dáta neobsahujú medzery alebo nesprávne hodnoty. Formát výstupu Dopyt by mal vrátiť tabuľku s poľami v takom poradí: • campaign_name (string) — názov reklamnej kampane • total_events (int) — celkový počet udalostí súvisiacich s kampaňou • unique_users (int) — počet jedinečných používateľov, ktorí vykonali udalosti • first_event_time (timestamp) — dátum a čas prvého udalosti kampane • last_event_time (timestamp) — dátum a čas posledného udalosti kampane • campaign_rank (int) — poradie kampane podľa zostupného počtu celkových udalostí Dáta zoradiť podľa poradia kampane podľa vzostupu, a potom podľa campaign_name v abecednom poradí.
sobes.tech AI
Odpoveď od AI
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.
Таким образом, получаем отчет с нужными метриками и ранжированием.