Junior
Analiza activității utilizatorilor în campaniile publicitare Compania păstrează evidența evenimentelor legate de campaniile publicitare. Sunt furnizate două tabele: • campaigns — lista campaniilor publicitare cu identificatorii și denumirile lor; • events — evenimentele utilizatorilor pe campanii, cu informații despre tipul evenimentului (de exemplu 'click' (clic)) și timpul. Este necesar să se creeze un raport pentru fiecare campanie cu următorii indicatori: • Numărul total de evenimente. • Numărul de utilizatori unici care au efectuat evenimente. • Timpul primului și ultimului eveniment pentru campanie. • Rangul campaniei în ordine descrescătoare după numărul total de evenimente. Rangul este un număr atribuit fiecărei linii în setul de rezultate pe baza ordinii datelor. Dacă două sau mai multe rânduri au aceeași valoare, primesc același rang, dar următorul rang este sărit. Raportul trebuie să includă doar campaniile care au avut evenimente: campaniile fără evenimente nu sunt luate în considerare. Raportul final trebuie să fie sortat mai întâi după rangul campaniei în ordine crescătoare, apoi după campaign_name în ordine alfabetică. Formatul de intrare • campaign_name (string) — numele campaniei publicitare • start_date (timestamp) — data și ora de început a campaniei • end_date (timestamp) — data și ora de sfârșit a campaniei Tabelul events: • event_id (int) — identificator unic al evenimentului • user_id (int) — identificator unic al utilizatorului care a efectuat evenimentul • campaign_id (int) — identificator unic al campaniei • event_type (string) — tipul evenimentului, de exemplu 'click' sau 'conversion' • event_time (timestamp) — data și ora evenimentului Datele nu conțin valori lipsă sau incorecte. Formatul de ieșire Interogarea trebuie să returneze un tabel cu câmpurile în această ordine: • campaign_name (string) — numele campaniei publicitare • total_events (int) — numărul total de evenimente legate de campanie • unique_users (int) — numărul de utilizatori unici care au efectuat evenimente • first_event_time (timestamp) — data și ora primului eveniment al campaniei • last_event_time (timestamp) — data și ora ultimului eveniment al campaniei • campaign_rank (int) — rangul campaniei în ordine descrescătoare după numărul total de evenimente Sortați datele după rangul campaniei în ordine crescătoare, apoi după campaign_name în ordine alfabetică.
sobes.tech AI
Răspuns de la 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.
Таким образом, получаем отчет с нужными метриками и ранжированием.