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