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