Junior

Vartotojų aktyvumo analizė reklamos kampanijose Įmonė stebi įvykius, susijusius su reklamos kampanijomis. Įvesties lentelės: • campaigns — reklamos kampanijų sąrašas su jų identifikatoriais ir pavadinimais; • events — vartotojų įvykiai pagal kampanijas su informacija apie įvykio tipą (pavyzdžiui, 'click' (paspaudimas ant reklamos)) ir laiką. Reikia sudaryti ataskaitą kiekvienai reklamos kampanijai su šiais rodikliais: • Bendra įvykių skaičius. • Unikalių vartotojų, kurie atliko įvykius, skaičius. • Kampanijos pirmojo ir paskutinio įvykio laikas. • Kampanijos reitingas pagal bendrą įvykių skaičių mažėjimo tvarka. Reitingas — skaičius, kuris priskiriamas kiekvienai eilutei rezultato rinkinyje pagal nurodytą duomenų tvarką. Jei dvi ar daugiau eilučių turi tą pačią reikšmę, joms priskiriamas tas pats reitingas, tačiau sekantis reitingas praleidžiamas. Į ataskaitą įtraukiamos tik kampanijos, kurios turėjo įvykių: be įvykių esančios kampanijos neįtraukiamos. Galutinis ataskaita turi būti pirmiausia surūšiuota pagal reitingą mažėjimo tvarka, o tada pagal campaign_name abėcėlės tvarka. Įvesties formatas • campaign_name (string) — reklamos kampanijos pavadinimas • start_date (timestamp) — kampanijos pradžios data ir laikas • end_date (timestamp) — kampanijos pabaigos data ir laikas Įvykiai: • event_id (int) — unikalus įvykio identifikatorius • user_id (int) — unikalus vartotojo identifikatorius, kuris atliko įvykį • campaign_id (int) — unikalus kampanijos identifikatorius • event_type (string) — įvykio tipas, pavyzdžiui, 'click' (paspaudimas) arba 'conversion' (konversija — sėkmingas veiksmas, pavyzdžiui, pirkimas) • event_time (timestamp) — įvykio atlikimo laikas Duomenys nesudaro tarpelio ar klaidingų reikšmių. Išvesties formatas Užklausa turi grąžinti lentelę su laukais tokia tvarka: • campaign_name (string) — reklamos kampanijos pavadinimas • total_events (int) — bendras įvykių skaičius • unique_users (int) — unikalių vartotojų skaičius, kurie atliko įvykius • first_event_time (timestamp) — pirmojo įvykio laikas • last_event_time (timestamp) — paskutinio įvykio laikas • campaign_rank (int) — kampanijos reitingas pagal bendrą įvykių skaičių mažėjimo tvarka Duomenys turi būti surūšiuoti pagal reitingą mažėjimo tvarka, o tada pagal campaign_name abėcėlės tvarka.

sobes.tech AI

Atsakymas iš 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.

Таким образом, получаем отчет с нужными метриками и ранжированием.

Ankstesnis Kitas