Junior
Ανάλυση δραστηριότητας χρηστών σε διαφημιστικές καμπάνιες Η εταιρεία διατηρεί αρχείο γεγονότων που σχετίζονται με διαφημιστικές καμπάνιες. Παρέχονται δύο πίνακες: • campaigns — λίστα διαφημιστικών καμπανιών με τους αναγνωριστικούς και ονόματά τους; • events — γεγονότα χρηστών ανά καμπάνια με πληροφορίες σχετικά με τον τύπο του γεγονότος (π.χ. 'click' (κλικ)) και τον χρόνο. Απαιτείται η δημιουργία αναφοράς για κάθε καμπάνια με τα ακόλουθα δείκτες: • Συνολικός αριθμός γεγονότων. • Αριθμός μοναδικών χρηστών που πραγματοποίησαν γεγονότα. • Χρόνος του πρώτου και τελευταίου γεγονότος ανά καμπάνια. • Κατάταξη της καμπάνιας κατά φθίνουσα σειρά του συνολικού αριθμού γεγονότων. Η κατάταξη είναι ένας αριθμός που αποδίδεται σε κάθε γραμμή στο σύνολο αποτελεσμάτων βάσει της σειράς των δεδομένων. Αν δύο ή περισσότερες γραμμές έχουν την ίδια τιμή, λαμβάνουν την ίδια κατάταξη, αλλά η επόμενη κατάταξη παραλείπεται. Η αναφορά πρέπει να περιλαμβάνει μόνο καμπάνιες που είχαν γεγονότα: οι καμπάνιες χωρίς γεγονότα δεν λαμβάνονται υπόψη. Το τελικό αναφορά πρέπει να ταξινομηθεί πρώτα κατά την κατάταξη της καμπάνιας σε αύξουσα σειρά, και στη συνέχεια κατά το campaign_name σε αλφαβητική σειρά. Μορφή εισόδου • campaign_name (string) — όνομα διαφημιστικής καμπάνιας • start_date (timestamp) — ημερομηνία και ώρα έναρξης της καμπάνιας • end_date (timestamp) — ημερομηνία και ώρα λήξης της καμπάνιας Πίνακας events: • event_id (int) — μοναδικός αναγνωριστικός αριθμός γεγονότος • user_id (int) — μοναδικός αναγνωριστικός αριθμός χρήστη που πραγματοποίησε το γεγονός • campaign_id (int) — μοναδικός αναγνωριστικός αριθμός καμπάνιας • event_type (string) — τύπος γεγονότος, π.χ. 'click' ή 'conversion' • event_time (timestamp) — ημερομηνία και ώρα γεγονότος Τα δεδομένα δεν περιέχουν ελλείποντα ή λανθασμένα στοιχεία. Μορφή εξόδου Το ερώτημα πρέπει να επιστρέφει έναν πίνακα με τα πεδία με αυτή τη σειρά: • campaign_name (string) — όνομα διαφημιστικής καμπάνιας • total_events (int) — συνολικός αριθμός γεγονότων που σχετίζονται με την καμπάνια • unique_users (int) — αριθμός μοναδικών χρηστών που πραγματοποίησαν γεγονότα • first_event_time (timestamp) — ημερομηνία και ώρα του πρώτου γεγονότος της καμπάνιας • last_event_time (timestamp) — ημερομηνία και ώρα του τελευταίου γεγονότος της καμπάνιας • campaign_rank (int) — κατάταξη της καμπάνιας κατά φθίνουσα σειρά του συνολικού αριθμού γεγονότων Ταξινομήστε τα δεδομένα κατά την κατάταξη της καμπάνιας σε αύξουσα σειρά, και στη συνέχεια κατά το campaign_name σε αλφαβητική σειρά.
sobes.tech AI
Απάντηση από 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.
Таким образом, получаем отчет с нужными метриками и ранжированием.