Junior
მომხმარებელთა აქტივობის ანალიზი რეკლამების კამპანიებში კომპანია აკონტროლებს მოვლენებს, რომლებიც დაკავშირებულია რეკლამების კამპანიებთან. შესასვლელი ცხრილები: • campaigns — რეკლამების კამპანიების სია, მათ იდენტიფიკატორებთან და სახელწოდებებთან; • events — მომხმარებლების მოვლენები კამპანიების მიხედვით, მათ შორის მოვლენების ტიპი (მაგალითად, 'click' (კლიკი რეკლამაზე)) და დრო. საჭიროა თითოეული რეკლამების კამპანიისთვის ანგარიშის შექმნა შემდეგი მაჩვენებლებით: • საერთო მოვლენების რაოდენობა. • უნიკალური მომხმარებლების რაოდენობა, რომლებმაც განახორციელეს მოვლენები. • კამპანიის პირველი და ბოლო მოვლენების დრო. • კამპანიის რეიტინგი საერთო მოვლენების რაოდენობის მიხედვით კლებადობით. რეიტინგი — რიცხვი, რომელიც მიენიჭება თითოეულ ხაზს შედეგების ნაკრებში მონაცემების მითითებულ წესზე დაყრდნობით. თუ ორი ან მეტი ხაზი აქვს ერთნაირი მნიშვნელობა, მათ მიენიჭებათ ერთნაირი რეიტინგი, მაგრამ შემდეგი რეიტინგი გამოტოვებულია. შესაბამისი კამპანიები, რომელთაც ჰქონდათ მოვლენები, ანგარიშში შედიან: მოვლენების გარეშე კამპანიები არ განიხილება. საბოლოო ანგარიში უნდა იყოს პირველ რიგში რეიტინგით ზრდადობით, შემდეგ კი campaign_name-ის მიხედვით ალფავიტურად. შესასვლელი ფორმატი • campaign_name (string) — რეკლამების კამპანიის სახელი • start_date (timestamp) — კამპანიის დაწყების დრო • end_date (timestamp) — კამპანიის დასრულების დრო მონაცემთა ცხრილი: • 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.
Таким образом, получаем отчет с нужными метриками и ранжированием.