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.
Таким образом, получаем отчет с нужными метриками и ранжированием.