Junior
Lietotāju aktivitātes analīze reklāmas kampaņās Uzņēmums uzskaita notikumus, kas saistīti ar reklāmas kampaņām. Ievades tabulas: • campaigns — reklāmas kampaņu saraksts ar to identifikatoriem un nosaukumiem; • events — lietotāju notikumi pēc kampaņām ar informāciju par notikuma tipu (piemēram, 'click' (klikšķis uz reklāmas)) un laiku. Nepieciešams izveidot pārskatu par katru reklāmas kampaņu ar šādiem rādītājiem: • Kopējais notikumu skaits. • Unikālo lietotāju skaits, kuri veica notikumus. • Kampaņas pirmā un pēdējā notikuma laiks. • Kampaņas ranga pēc kopējā notikumu skaita dilstošā secībā. Rangs — skaitlis, kas piešķirts katrai rindai rezultātu kopā, balstoties uz norādīto datu kārtību. Ja divas vai vairāk rindas ir ar vienādu vērtību, tām piešķir vienādu rangu, bet nākamais rangs tiek izlaists. Tiek iekļautas tikai tās kampaņas, kurām bija notikumi: bez notikumiem esošas kampaņas netiek ņemtas vērā. Galīgais pārskats ir jāsakārto vispirms pēc ranga dilstošā secībā, pēc tam pēc campaign_name alfabētiskā secībā. Ievades formāts • campaign_name (string) — reklāmas kampaņas nosaukums • start_date (timestamp) — kampaņas sākuma datums un laiks • end_date (timestamp) — kampaņas beigu datums un laiks Notikumu tabula: • event_id (int) — unikāls notikuma identifikators • user_id (int) — unikāls lietotāja identifikators, kurš veica notikumu • campaign_id (int) — reklāmas kampaņas unikāls identifikators • event_type (string) — notikuma veids, piemēram, 'click' (klikšķis) vai 'conversion' (konversija — veiksmīga darbība, piemēram, pirkums) • event_time (timestamp) — notikuma veikšanas laiks Dati nesatur tukšas vai nepareizas vērtības. Izvades formāts Vaicājumam jāatgriež tabula ar šādiem laukiem: • campaign_name (string) — reklāmas kampaņas nosaukums • total_events (int) — kopējais notikumu skaits • unique_users (int) — unikālo lietotāju skaits, kuri veica notikumus • first_event_time (timestamp) — pirmā notikuma laiks • last_event_time (timestamp) — pēdējā notikuma laiks • campaign_rank (int) — kampaņas ranga pēc kopējā notikumu skaita dilstošā secībā Datus jāizkārto pēc ranga dilstošā secībā, pēc tam pēc campaign_name alfabētiskā secībā.
sobes.tech AI
Atbilde no 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.
Таким образом, получаем отчет с нужными метриками и ранжированием.