Junior
Analýza aktivity uživatelů v reklamních kampaních Společnost vede záznam událostí souvisejících s reklamními kampaněmi. Jsou poskytnuty dvě tabulky: • campaigns — seznam reklamních kampaní s jejich identifikátory a názvy; • events — události uživatelů podle kampaní s informacemi o typu události (například 'click' (klik)) a čase. Je nutné vytvořit zprávu pro každou kampaň s následujícími ukazateli: • Celkový počet událostí. • Počet unikátních uživatelů, kteří události provedli. • Čas prvního a posledního události podle kampaně. • Pořadí kampaně podle klesajícího celkového počtu událostí. Pořadí je číslo přiřazené každému řádku ve výsledkové sadě na základě pořadí dat. Pokud mají dvě nebo více řádků stejnou hodnotu, dostanou stejnou pozici, ale následující pozice je přeskočena. Zpráva by měla obsahovat pouze kampaně, které měly události: kampaně bez událostí se nezahrnují. Finální zpráva by měla být seřazena nejdříve podle pořadí kampaně vzestupně, a poté podle campaign_name v abecedním pořadí. Formát vstupu • campaign_name (string) — název reklamní kampaně • start_date (timestamp) — datum a čas začátku kampaně • end_date (timestamp) — datum a čas konce kampaně Tabulka events: • event_id (int) — unikátní identifikátor události • user_id (int) — unikátní identifikátor uživatele, který provedl událost • campaign_id (int) — unikátní identifikátor kampaně • event_type (string) — typ události, například 'click' nebo 'conversion' • event_time (timestamp) — datum a čas události Data neobsahují chybějící nebo nesprávné hodnoty. Formát výstupu Dotaz by měl vrátit tabulku se sloupci ve správném pořadí: • campaign_name (string) — název reklamní kampaně • total_events (int) — celkový počet událostí souvisejících s kampaní • unique_users (int) — počet unikátních uživatelů, kteří provedli události • first_event_time (timestamp) — datum a čas prvního události kampaně • last_event_time (timestamp) — datum a čas posledního události kampaně • campaign_rank (int) — pořadí kampaně podle klesajícího celkového počtu událostí Seřadit data podle pořadí kampaně vzestupně, a poté podle campaign_name v abecedním pořadí.
sobes.tech AI
Odpověď 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.
Таким образом, получаем отчет с нужными метриками и ранжированием.