Junior

Analiza aktywności użytkowników w kampaniach reklamowych Firma prowadzi rejestr zdarzeń związanych z kampaniami reklamowymi. Dostarczone są dwie tabele: • campaigns — lista kampanii reklamowych z ich identyfikatorami i nazwami; • events — zdarzenia użytkowników według kampanii z informacją o typie zdarzenia (np. 'click' (klik)) i czasie. Należy sporządzić raport dla każdej kampanii z następującymi wskaźnikami: • Łączna liczba zdarzeń. • Liczba unikalnych użytkowników, którzy wykonali zdarzenia. • Czas pierwszego i ostatniego zdarzenia dla kampanii. • Ranga kampanii w kolejności malejącej według łącznej liczby zdarzeń. Ranga to liczba przypisana do każdego wiersza w zestawie wyników na podstawie kolejności danych. Jeśli dwie lub więcej linii mają tę samą wartość, otrzymują tę samą rangę, ale następny ranga jest pomijana. Raport powinien obejmować tylko kampanie, które miały zdarzenia: kampanie bez zdarzeń nie są brane pod uwagę. Ostateczny raport powinien być posortowany najpierw według rangi kampanii rosnąco, a następnie według campaign_name w porządku alfabetycznym. Format wejścia • campaign_name (string) — nazwa kampanii reklamowej • start_date (timestamp) — data i czas rozpoczęcia kampanii • end_date (timestamp) — data i czas zakończenia kampanii Tabela events: • event_id (int) — unikalny identyfikator zdarzenia • user_id (int) — unikalny identyfikator użytkownika, który wykonał zdarzenie • campaign_id (int) — unikalny identyfikator kampanii • event_type (string) — typ zdarzenia, np. 'click' lub 'conversion' • event_time (timestamp) — data i czas zdarzenia Dane nie zawierają braków ani nieprawidłowych wartości. Format wyjścia Zapytanie powinno zwrócić tabelę z polami w takiej kolejności: • campaign_name (string) — nazwa kampanii reklamowej • total_events (int) — łączna liczba zdarzeń związanych z kampanią • unique_users (int) — liczba unikalnych użytkowników, którzy wykonali zdarzenia • first_event_time (timestamp) — data i czas pierwszego zdarzenia kampanii • last_event_time (timestamp) — data i czas ostatniego zdarzenia kampanii • campaign_rank (int) — ranga kampanii według malejącej liczby zdarzeń Posortuj dane według rangi kampanii rosnąco, a następnie według campaign_name w porządku alfabetycznym.

sobes.tech AI

Odpowiedź 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.

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

Poprzednia Dalej