Junior
Análise da atividade dos utilizadores em campanhas publicitárias A empresa mantém um registo de eventos relacionados com campanhas publicitárias. São fornecidas duas tabelas: • campaigns — lista de campanhas publicitárias com os seus identificadores e nomes; • events — eventos de utilizadores por campanha com informações sobre o tipo de evento (por exemplo, 'click' (clique na publicidade)) e o tempo. É necessário criar um relatório para cada campanha com os seguintes indicadores: • Número total de eventos. • Número de utilizadores únicos que realizaram eventos. • Hora do primeiro e do último evento por campanha. • Classificação da campanha por ordem decrescente do número total de eventos. A classificação é um número atribuído a cada linha no conjunto de resultados com base na ordem dos dados. Se duas ou mais linhas tiverem o mesmo valor, recebem a mesma classificação, mas a classificação seguinte é pulada. O relatório deve incluir apenas campanhas que tiveram eventos: campanhas sem eventos não são consideradas. O relatório final deve ser ordenado primeiro pela classificação da campanha em ordem crescente, e depois por campaign_name em ordem alfabética. Formato de entrada • campaign_name (string) — nome da campanha publicitária • start_date (timestamp) — data e hora de início da campanha • end_date (timestamp) — data de fim da campanha Tabela events: • event_id (int) — identificador único do evento • user_id (int) — identificador único do utilizador que realizou o evento • campaign_id (int) — identificador único da campanha • event_type (string) — tipo de evento, por exemplo 'click' ou 'conversion' • event_time (timestamp) — data e hora do evento Os dados não contêm valores em falta ou incorretos. Formato de saída A consulta deve retornar uma tabela com os campos nesta ordem: • campaign_name (string) — nome da campanha publicitária • total_events (int) — número total de eventos relacionados com a campanha • unique_users (int) — número de utilizadores únicos que realizaram eventos • first_event_time (timestamp) — data e hora do primeiro evento da campanha • last_event_time (timestamp) — data e hora do último evento da campanha • campaign_rank (int) — classificação da campanha por ordem decrescente do número total de eventos Ordene os dados por classificação da campanha em ordem crescente, e depois por campaign_name em ordem alfabética.
sobes.tech IA
Resposta da IA
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.
Таким образом, получаем отчет с нужными метриками и ранжированием.