Назад к вопросам
Junior
11
Анализ активности пользователей в рекламных кампаниях Компания ведет учет событий, связанных с рекламными кампаниями. На вход подаются две таблицы: • 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 в алфавитном порядке.
Компании, где спрашивали
01.tech
Ответ от нейросети
sobes.tech 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.
Таким образом, получаем отчет с нужными метриками и ранжированием.