Junior

Analisi dell’attività degli utenti nelle campagne pubblicitarie L’azienda tiene traccia degli eventi relativi alle campagne pubblicitarie. Sono fornite due tabelle: • campaigns — elenco delle campagne pubblicitarie con i loro identificativi e nomi; • events — eventi degli utenti per campagna con informazioni sul tipo di evento (ad esempio 'click' (clic)) e sul tempo. È necessario creare un rapporto per ogni campagna con i seguenti indicatori: • Numero totale di eventi. • Numero di utenti unici che hanno effettuato eventi. • Ora del primo e dell’ultimo evento per campagna. • Classifica della campagna in ordine decrescente per numero totale di eventi. La classifica è un numero assegnato a ogni riga nel set di risultati basato sull’ordine dei dati. Se due o più righe hanno lo stesso valore, ricevono la stessa classifica, ma la classifica successiva viene saltata. Il rapporto deve includere solo le campagne che hanno avuto eventi: le campagne senza eventi non vengono considerate. Il rapporto finale deve essere ordinato prima per la classifica della campagna in ordine crescente, e poi per campaign_name in ordine alfabetico. Formato di input • campaign_name (string) — nome della campagna pubblicitaria • start_date (timestamp) — data e ora di inizio della campagna • end_date (timestamp) — data di fine della campagna Tabella events: • event_id (int) — identificatore unico dell’evento • user_id (int) — identificatore unico dell’utente che ha effettuato l’evento • campaign_id (int) — identificatore unico della campagna • event_type (string) — tipo di evento, ad esempio 'click' o 'conversion' • event_time (timestamp) — data e ora dell’evento I dati non contengono valori mancanti o incorretti. Formato di output La query deve restituire una tabella con i campi in questo ordine: • campaign_name (string) — nome della campagna pubblicitaria • total_events (int) — numero totale di eventi relativi alla campagna • unique_users (int) — numero di utenti unici che hanno effettuato eventi • first_event_time (timestamp) — data e ora del primo evento della campagna • last_event_time (timestamp) — data e ora dell’ultimo evento della campagna • campaign_rank (int) — classifica della campagna in ordine decrescente del numero totale di eventi Ordinare i dati per il rango della campagna in ordine crescente, e poi per campaign_name in ordine alfabetico.

sobes.tech AI

Risposta dell'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.

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

Precedente Avanti