Glassdoor (Google) — N-day rolling DAU и WAU

Условие

Таблица events(user_id, event_date). Для каждой даты верните:

• DAU — уникальных за этот день,
• WAU — уникальных за последние 7 дней (включая сегодня),
• MAU — уникальных за последние 28 дней.

Решение

Подход — три коррелированных подзапроса или COUNT DISTINCT + window

Просто оконной функцией COUNT(DISTINCT) OVER не получится — DISTINCT в window-функциях не поддерживается (или плохо поддерживается). Поэтому через self-join или коррелированные подзапросы.

WITH dates AS (
  SELECT DISTINCT event_date FROM events
)
SELECT
  d.event_date,
  (SELECT COUNT(DISTINCT user_id) FROM events e
   WHERE e.event_date = d.event_date) AS dau,
  (SELECT COUNT(DISTINCT user_id) FROM events e
   WHERE e.event_date BETWEEN d.event_date - 6 AND d.event_date) AS wau,
  (SELECT COUNT(DISTINCT user_id) FROM events e
   WHERE e.event_date BETWEEN d.event_date - 27 AND d.event_date) AS mau
FROM dates d
ORDER BY d.event_date;

Через self-join (быстрее на больших данных)

WITH daily AS (
  SELECT event_date, user_id
  FROM events
  GROUP BY event_date, user_id
)
SELECT
  d.event_date,
  COUNT(DISTINCT CASE WHEN e.event_date = d.event_date THEN e.user_id END) AS dau,
  COUNT(DISTINCT CASE WHEN e.event_date BETWEEN d.event_date - 6 AND d.event_date
                      THEN e.user_id END) AS wau,
  COUNT(DISTINCT CASE WHEN e.event_date BETWEEN d.event_date - 27 AND d.event_date
                      THEN e.user_id END) AS mau
FROM (SELECT DISTINCT event_date FROM events) d
JOIN events e ON e.event_date BETWEEN d.event_date - 27 AND d.event_date
GROUP BY d.event_date
ORDER BY d.event_date;

Заметка

В BigQuery / Snowflake есть COUNT(DISTINCT) OVER или приближённые APPROX_COUNT_DISTINCT — на проде с TB-данными переходят на HyperLogLog.

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

1. COUNT(DISTINCT) OVER не работает в Postgres/MySQL. Стандартный ANSI window не допускает DISTINCT.
2. Календарь дат vs события. Если день без событий — он не появится в dates. Если просят непрерывную ось — generate_series.
3. Стартовый период. Первые 27 дней — MAU неполный (нет данных за «до старта»). Бизнесово часто это NULL/«N/A» в первый месяц.
4. DISTINCT в CASE внутри COUNT DISTINCT. Работает корректно: если CASE даёт NULL — не считается; если user_id — считается уникальным.

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

Либо коррелированные подзапросы (медленно, читаемо), либо self-join + COUNT DISTINCT CASE WHEN ... END (быстрее). На проде — pre-aggregated user_day таблица и rolling COUNT DISTINCT.