Junior
Analyse der Nutzeraktivität in Werbekampagnen Das Unternehmen führt eine Aufzeichnung der Ereignisse im Zusammenhang mit Werbekampagnen. Es werden zwei Tabellen bereitgestellt: • campaigns — Liste der Werbekampagnen mit ihren Identifikatoren und Namen; • events — Nutzerereignisse nach Kampagnen mit Informationen zum Ereignistyp (z.B. 'click' (Klick auf die Werbung)) und zur Zeit. Es ist notwendig, für jede Kampagne einen Bericht mit den folgenden Kennzahlen zu erstellen: • Gesamtzahl der Ereignisse. • Anzahl der eindeutigen Nutzer, die Ereignisse durchgeführt haben. • Zeitpunkt des ersten und letzten Ereignisses pro Kampagne. • Rang der Kampagne nach absteigender Gesamtzahl der Ereignisse. Der Rang ist eine Zahl, die jeder Zeile im Ergebnis basierend auf der Datenreihenfolge zugewiesen wird. Wenn zwei oder mehr Zeilen denselben Wert haben, erhalten sie den gleichen Rang, aber der nächste Rang wird übersprungen. Der Bericht sollte nur Kampagnen enthalten, bei denen Ereignisse stattgefunden haben: Kampagnen ohne Ereignisse werden nicht berücksichtigt. Das endgültige Ergebnis sollte zuerst nach dem Rang der Kampagne aufsteigend sortiert werden, und dann nach campaign_name in alphabetischer Reihenfolge. Eingabeformat • campaign_name (String) — Name der Werbekampagne • start_date (Timestamp) — Startdatum und -zeit der Kampagne • end_date (Timestamp) — Enddatum und -zeit der Kampagne Tabelle events: • event_id (int) — Eindeutige Ereignis-ID • user_id (int) — Eindeutige Benutzer-ID, die das Ereignis durchgeführt hat • campaign_id (int) — Eindeutige Kampagnen-ID • event_type (String) — Art des Ereignisses, z.B. 'click' oder 'conversion' • event_time (Timestamp) — Datum und Uhrzeit des Ereignisses Die Daten enthalten keine fehlenden oder inkorrekten Werte. Ausgabeformat Die Abfrage sollte eine Tabelle mit den folgenden Feldern in dieser Reihenfolge zurückgeben: • campaign_name (String) — Name der Werbekampagne • total_events (int) — Gesamtzahl der Ereignisse im Zusammenhang mit der Kampagne • unique_users (int) — Anzahl der eindeutigen Benutzer, die Ereignisse durchgeführt haben • first_event_time (Timestamp) — Datum und Uhrzeit des ersten Ereignisses der Kampagne • last_event_time (Timestamp) — Datum und Uhrzeit des letzten Ereignisses der Kampagne • campaign_rank (int) — Rang der Kampagne nach absteigender Gesamtzahl der Ereignisse Sortieren Sie die Daten nach dem Rang der Kampagne aufsteigend, und dann nach campaign_name in alphabetischer Reihenfolge.
sobes.tech KI
Antwort von 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.
Таким образом, получаем отчет с нужными метриками и ранжированием.