Dodo Brands — подневная динамика 30-дневной активной базы по городам

Условие

В компании замеряется «30-дневная активная база» — для любого дня D это число уникальных клиентов, совершивших визит за 30 дней до D включительно (то есть на интервале [D − 29, D]).

Таблица чеков с полями: cityname, date, orderid, clientid, sales. Данные с 2022-01-01 по 2022-06-30.

Посчитайте подневную динамику 30-дневной активной базы по каждому городу. Сортировка — по городу, дате.

Пример вывода:

Решение

Подход

«Подневная 30-day distinct» — это rolling distinct count. SQL это плохо умеет нативно: COUNT(DISTINCT) OVER (...) без фрейма не работает в PostgreSQL/MySQL стандартно. Идиоматичных подхода два:

1. Self-join: для каждой даты D присоединить чеки с date BETWEEN D-29 AND D и взять COUNT(DISTINCT clientid).
2. Window-trick через арриваль и убытие пользователя в окне — сложнее.

Вариант (1) проще читать, но дороже по ресурсам; (2) — оптимизирован.

Реализация (вариант 1, читаемый)

WITH days AS (
  SELECT DISTINCT cityname,
         d::date AS day
  FROM checks
  CROSS JOIN generate_series(DATE '2022-01-30', DATE '2022-06-30', INTERVAL '1 day') AS g(d)
),
joined AS (
  SELECT dy.cityname, dy.day, c.clientid
  FROM days dy
  LEFT JOIN checks c
    ON c.cityname = dy.cityname
   AND c.date BETWEEN dy.day - INTERVAL '29 day' AND dy.day
)
SELECT cityname, day AS date,
       COUNT(DISTINCT clientid) AS active_base
FROM joined
GROUP BY cityname, day
ORDER BY cityname, day;

Заметим: 2022-01-30 — первая дата, для которой существует полное окно [2022-01-01, 2022-01-30]. Обычно «активная база» определяется ровно для дат, где окно полное; иначе формально для 2022-01-15 это будет «15-дневная база».

Реализация (вариант 2, оптимизированный — Postgres ≥ 15 / MSSQL)

Идея: для каждого clientid вместо «есть ли заказ в окне» считаем «остаётся ли клиент активным сегодня». Каждый клиент влияет на окно [order_date, order_date + 29]. Можно превратить в проблему «count of intervals overlapping each day» через +1 при входе, -1 при выходе, а затем кумулятивная сумма.

WITH events AS (
  SELECT cityname, clientid, date              AS day, +1 AS delta FROM checks
  UNION ALL
  SELECT cityname, clientid, date + INTERVAL '30 day' AS day, -1 AS delta FROM checks
),
-- distinct users only once per (city, day). Берем "первое появление в окне"
-- более аккуратно делается через DENSE_RANK по последнему чеку.
...

На практике вариант 1 + индекс (cityname, date, clientid) достаточно. Если данных много — материализуйте промежуточный массив.

Альтернатива на MySQL (без generate_series)

Сгенерируйте календарь во вспомогательной таблице calendar(day DATE), дальше идентично варианту 1.

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

1. Окно [D−30, D) vs [D−29, D]: классическое определение «30 дней включая сегодня» — это [D−29, D] (всего 30 дней). Перепроверьте требование заказчика.
2. COUNT(DISTINCT) в OVER: не работает в большинстве СУБД с rolling-фреймом — поэтому нужен self-join или приёмы выше.
3. Cross join по городам: если города не в каждом дне присутствуют, LEFT JOIN с фильтром по городу обязателен — иначе некоторые комбинации потеряются.
4. Часовой пояс / date vs timestamp: если date — timestamp, нужно явно date(date).
5. Производительность: задача O(N × 30). На больших данных — материализация ежедневного снапшота активных клиентов.

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

CTE с генерацией календаря по городу × дню → LEFT JOIN с чеками, фильтр на [day−29, day] → COUNT(DISTINCT clientid) сгруппировать по (city, day) → отсортировать.