Условие

Структура данных:

content_watch (один просмотр — одна запись):

watch_id, show_date (дата-время), show_duration (секунды), platform (id площадки),
user_id, utm_medium (источник трафика), content_id.

content:

content_id, compilation_id (NULL для единичного контента — не серия), episode, paid_type (SVOD / AVOD / TVOD).

Задание 1: 3 SQL-запроса.

Количество просмотров по дням, отдельно по монетизации SVOD и AVOD, на платформах 10 и 11 за последние 30 дней.
Ежемесячный ТОП-5 сериалов и ТОП-5 единичного контента по числу смотрящих людей.
Список пользователей, у которых вчера был просмотр с organic, а сразу следующий — с referral.

Решение

Реализация

Запрос 1. Просмотры по монетизации за 30 дней

SELECT
  DATE(cw.show_date)                                        AS dt,
  c.paid_type,
  COUNT(*)                                                  AS views
FROM content_watch cw
JOIN content c ON c.content_id = cw.content_id
WHERE cw.platform IN (10, 11)
  AND DATE(cw.show_date) >= CURRENT_DATE - INTERVAL '30 day'
  AND DATE(cw.show_date) <  CURRENT_DATE
  AND c.paid_type IN ('SVOD', 'AVOD')
GROUP BY 1, 2
ORDER BY 1, 2;

«Сегодня» обычно неполный — фильтр < CURRENT_DATE исключает его.

Запрос 2. Топ-5 сериалов и единичного по месяцам

WITH watches AS (
  SELECT
    DATE_TRUNC('month', cw.show_date)::date            AS month,
    cw.user_id,
    c.compilation_id,
    c.content_id,
    -- Сериал = compilation_id NOT NULL; единичный = NULL
    CASE WHEN c.compilation_id IS NOT NULL
         THEN 'series' ELSE 'standalone' END           AS content_type,
    COALESCE(c.compilation_id, c.content_id)            AS content_key
  FROM content_watch cw
  JOIN content c ON c.content_id = cw.content_id
),
unique_viewers AS (
  -- Уникальные смотрящие по (месяц, тип, ключ контента)
  SELECT month, content_type, content_key,
         COUNT(DISTINCT user_id) AS uniq_viewers
  FROM watches
  GROUP BY 1, 2, 3
),
ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY month, content_type
                            ORDER BY uniq_viewers DESC) AS rn
  FROM unique_viewers
)
SELECT month, content_type, content_key, uniq_viewers
FROM ranked
WHERE rn <= 5
ORDER BY month, content_type, rn;

Ключевые моменты:

compilation_id NOT NULL → сериал (серия объединена в compilation). Считаем уникальных по compilation, не по episode.
Для единичного контента используем сам content_id как ключ.
ROW_NUMBER OVER (PARTITION BY month, content_type ORDER BY uniq_viewers DESC) — топ-5 в каждой группе.
Для решения «вместе сериалов и фильмов в одном топ-5» — нужно убрать content_type из PARTITION BY. Но условие говорит «ТОП-5 сериалов и ТОП-5 единичного» — две отдельные таблицы, поэтому раздельно.

Запрос 3. Юзеры с переходом organic → referral

WITH yesterday AS (
  SELECT
    user_id,
    show_date,
    LOWER(utm_medium)                       AS utm_medium_norm,
    LAG(LOWER(utm_medium)) OVER (PARTITION BY user_id ORDER BY show_date) AS prev_utm
  FROM content_watch
  WHERE DATE(show_date) = CURRENT_DATE - INTERVAL '1 day'
)
SELECT DISTINCT user_id
FROM yesterday
WHERE utm_medium_norm = 'referral'
  AND prev_utm = 'organic';

Ключевые моменты:

LOWER нужен — в данных встречается organic, Organic, Referral в разном регистре.
LAG даёт предыдущий просмотр в рамках вчерашнего дня и одного юзера.
Условие — текущий = referral, предыдущий = organic.

Тонкость: «сразу следующий» — означает, что между organic и referral не было других просмотров. LAG это и проверяет.

Если нужно «когда-либо за день, organic → referral в любой точке последовательности» — то же.

Анализ / интерпретация

Эти 3 запроса покрывают типовые задачи аналитики:

(1) Tracking метрики по платформам и монетизации.
(2) Топ-контент для мониторинга трендов.
(3) Поведенческие паттерны — переход utm-каналов (полезно для атрибуции).

Подводные камни

«Последние 30 дней» — включая сегодняшний неполный день или нет? Фильтруем < CURRENT_DATE.
Часовые пояса. DATE(show_date) — на сервере в UTC. Если задача про московское время — (show_date AT TIME ZONE 'Europe/Moscow')::date.
«Топ-5» с дубликатами. Если у двух сериалов одинаковое число зрителей — нужен tie-breaker. ROW_NUMBER детерминирует, но скрывает ties; RANK показывает.
«Уникальные люди» vs «уникальные просмотры». Условие — по людям, поэтому COUNT(DISTINCT user_id).
compilation_id IS NULL — единичный контент. Для сериалов смотрим compilation, не episode. Часто путают.
utm_medium регистр — частая ошибка. Organic ≠ organic.
LAG без сортировки — нестабилен. Всегда указывайте ORDER BY.
LAG в рамках одного дня. Партиционирование по user_id — да, но фильтр WHERE DATE(show_date) = вчера нужен снаружи. Если нужны переходы через несколько дней — фильтр иначе.
Топ-5 в одном месяце vs «топ-5 за всё время с группировкой по месяцу». Разные задачи — уточняйте.

Альтернативы

Для запроса 3 (LAG) альтернатива — самосоединение:

SELECT DISTINCT a.user_id
FROM content_watch a
JOIN content_watch b
  ON b.user_id = a.user_id
 AND b.show_date > a.show_date
 AND NOT EXISTS (
   SELECT 1 FROM content_watch c
   WHERE c.user_id = a.user_id AND c.show_date > a.show_date AND c.show_date < b.show_date
 )
WHERE DATE(a.show_date) = CURRENT_DATE - INTERVAL '1 day'
  AND LOWER(a.utm_medium) = 'organic'
  AND LOWER(b.utm_medium) = 'referral';

Тяжелее по перформансу, но иногда читаемо.

Эталонный ответ

Запрос 1: JOIN content, фильтр platform IN (10,11), paid_type IN ('SVOD','AVOD'), DATE >= CURRENT_DATE - 30. Группировка по дате и paid_type.

Запрос 2: CTE с уникальными зрителями по compilation_id (сериал) или content_id (фильм), ROW_NUMBER OVER (PARTITION BY month, type ORDER BY uniq DESC) <= 5.

Запрос 3: LAG(utm_medium) по user_id с фильтром на вчера; current = referral, prev = organic. Не забыть LOWER.

ivi — SQL: дневные просмотры по монетизации, топ-контент, organic→referral переходы