Junior

Kasutajategevuse analüüs reklaamikampaaniates Ettevõte jälgib sündmusi, mis on seotud reklaamikampaaniatega. Sisendtabelid: • campaigns — reklaamikampaaniate nimekiri nende identifikaatoritega ja nimedega; • events — kasutajate sündmused kampaaniate kaupa, koos teabe ja tüübi (näiteks 'click' (klõps reklaamil)) ning aja kohta. Vajalik on koostada aruanne iga reklaamikampaania kohta järgmiste näitajate põhjal: • Üldine sündmuste arv. • Unikaalsete kasutajate arv, kes on sündmusi teinud. • Kampaania esimese ja viimase sündmuse aeg. • Kampaania järjestus üldise sündmuste arvu järgi kahanevas järjekorras. Ränd — number, mis antakse igale reale tulemuste kogumis vastavalt määratud andmetele. Kui kaks või rohkem rida on sama väärtusega, saavad nad sama ränga, kuid järgmine ränga jäetakse vahele. Arvesse võetakse ainult need kampaaniad, millel olid sündmused: kampaaniaid ilma sündmusteta ei arvestata. Lõplik aruanne peab olema esmalt sorteeritud rändi järgi kahanevas järjekorras ning seejärel campaign_name alfabetaalselt. Sisendvorming • campaign_name (string) — reklaamikampaania nimi • start_date (timestamp) — kampaania alguskuupäev ja kellaaeg • end_date (timestamp) — kampaania lõppkuupäev ja kellaaeg Sündmuste tabel: • event_id (int) — unikaalne sündmuse identifikaator • user_id (int) — unikaalne kasutaja identifikaator, kes tegi sündmuse • campaign_id (int) — reklaamikampaania unikaalne identifikaator • event_type (string) — sündmuse tüüp, näiteks 'click' (klõps) või 'conversion' (konversioon — edukas tegevus, näiteks ost) • event_time (timestamp) — sündmuse toimumise aeg Andmed ei sisalda tühje või valesid väärtusi. Väljundi formaat Päring peaks tagastama tabeli järgmiste väljadega: • campaign_name (string) — reklaamikampaania nimi • total_events (int) — üldine sündmuste arv • unique_users (int) — unikaalsete kasutajate arv, kes tegi sündmusi • first_event_time (timestamp) — esimese sündmuse aeg • last_event_time (timestamp) — viimase sündmuse aeg • campaign_rank (int) — kampaania järjestus üldise sündmuste arvu järgi kahanevas järjekorras Andmed tuleb sorteerida rändi järgi kahanevas järjekorras ning seejärel campaign_name alfabetaalselt.

sobes.tech AI

Vastus AI-lt

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.

Таким образом, получаем отчет с нужными метриками и ранжированием.

Eelmine Edasi