Junior
Анализа активности корисника у рекламних кампања Компанија води евиденцију догађаја повезаних са рекламним кампањама. Улазне табеле су: • campaigns — листа рекламних кампања са њиховим идентификаторима и називима; • events — догађаји корисника по кампањама са информацијама о типу догађаја (на пример 'click' (клик на оглас)) и времену. Потребно је формирати извештај за сваку рекламну кампању са следећим показатељима: • Укупни број догађаја. • Број јединствених корисника који су извршили догађаје. • Време првог и последњег догађаја по кампањи. • Ранг кампање по опадајућем броју укупних догађаја. Ранг — број који се додељује свакој линији у резултујућем скупу на основу задатог редоследа података. Ако две или више линија имају исту вредност, добијају исти ранг, али следећи ранг се прескаче. У извештај укључују се само кампање које су имале догађаје: кампање без догађаја се не узимају у обзир. Коначни извештај треба прво сортирати по рангу кампање по растућем редоследу, а затим по campaign_name у азбучном реду. Формат улаза • campaign_name (string) — назив рекламне кампање • start_date (timestamp) — датум и време почетка рекламне кампање • end_date (timestamp) — датум и време краја рекламне кампање Табела events: • event_id (int) — јединствени идентификатор догађаја • user_id (int) — јединствени идентификатор корисника који је извршио догађај • campaign_id (int) — јединствени идентификатор рекламне кампање • event_type (string) — тип догађаја, на пример 'click' (клик на оглас) или 'conversion' (конверзија — успешна акција, на пример куповина) • event_time (timestamp) — датум и време извршења догађаја Подаци не садрже пропусте или некоректне вредности. Формат излаза Упит треба да врати табелу са пољима у овом редоследу: • campaign_name (string) — назив рекламне кампање • total_events (int) — укупни број догађаја повезаних са кампањом • unique_users (int) — број јединствених корисника који су извршили догађаје • first_event_time (timestamp) — датум и време првог догађаја кампање • last_event_time (timestamp) — датум и време последњег догађаја кампање • campaign_rank (int) — ранг кампање по опадајућем броју укупних догађаја Податке сортирајте по рангу кампање по растућем редоследу, а затим по campaign_name у азбучном реду.
sobes.tech АИ
Одговор од АИ
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.
Таким образом, получаем отчет с нужными метриками и ранжированием.