Junior

Analyse de l’activité des utilisateurs dans les campagnes publicitaires L’entreprise tient un registre des événements liés aux campagnes publicitaires. Deux tableaux sont fournis: • campaigns — liste des campagnes publicitaires avec leurs identifiants et noms; • events — événements des utilisateurs par campagne avec des informations sur le type d’événement (par exemple 'click' (clic sur la publicité)) et le temps. Il est nécessaire de générer un rapport pour chaque campagne avec les indicateurs suivants: • Nombre total d’événements. • Nombre d’utilisateurs uniques ayant effectué des événements. • Temps du premier et du dernier événement par campagne. • Rang de la campagne par ordre décroissant du nombre total d’événements. Le rang est un nombre attribué à chaque ligne dans le jeu de résultats basé sur l’ordre des données. Si deux ou plusieurs lignes ont la même valeur, elles reçoivent le même rang, mais le rang suivant est sauté. Le rapport doit inclure uniquement les campagnes ayant eu des événements : les campagnes sans événements ne sont pas prises en compte. Le rapport final doit être trié d’abord par le rang de la campagne en ordre croissant, puis par campaign_name en ordre alphabétique. Format d’entrée • campaign_name (string) — nom de la campagne publicitaire • start_date (timestamp) — date et heure de début de la campagne • end_date (timestamp) — date et heure de fin de la campagne Tableau events: • event_id (int) — identifiant unique de l’événement • user_id (int) — identifiant unique de l’utilisateur ayant effectué l’événement • campaign_id (int) — identifiant unique de la campagne • event_type (string) — type d’événement, par exemple 'click' ou 'conversion' • event_time (timestamp) — date et heure de l’événement Les données ne contiennent pas de valeurs manquantes ou incorrectes. Format de sortie La requête doit retourner un tableau avec les champs dans cet ordre: • campaign_name (string) — nom de la campagne publicitaire • total_events (int) — nombre total d’événements liés à la campagne • unique_users (int) — nombre d’utilisateurs uniques ayant effectué des événements • first_event_time (timestamp) — date et heure du premier événement de la campagne • last_event_time (timestamp) — date et heure du dernier événement de la campagne • campaign_rank (int) — rang de la campagne par ordre décroissant du nombre total d’événements Trier les données par le rang de la campagne en ordre croissant, puis par campaign_name en ordre alphabétique.

sobes.tech IA

Réponse de l'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.

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

Précédent Suivant