Хабр SQL — DAU, MAU и stickiness одним запросом

Условие

Таблица events(user_id int, event_time timestamp). Постройте по дням за последние 90 дней:

• dau — уникальных пользователей за день,
• mau_30d — уникальных пользователей за последние 30 дней (rolling),
• stickiness = dau / mau_30d.

Решение

Подход

Сначала считаем дневной список «user × day», затем для каждой даты ищем DAU и MAU. Самое аккуратное — сделать DAU агрегированный, а MAU — оконкой по дням.

WITH daily AS (
  SELECT
    DATE(event_time) AS d,
    user_id
  FROM events
  WHERE event_time >= now() - INTERVAL '120 days'
  GROUP BY 1, 2
),
dau AS (
  SELECT d, COUNT(*) AS dau
  FROM daily
  GROUP BY d
)
SELECT
  d.d,
  d.dau,
  (SELECT COUNT(DISTINCT user_id)
   FROM daily x
   WHERE x.d BETWEEN d.d - INTERVAL '29 days' AND d.d) AS mau_30d,
  ROUND(
    100.0 * d.dau /
      NULLIF((SELECT COUNT(DISTINCT user_id)
              FROM daily x
              WHERE x.d BETWEEN d.d - INTERVAL '29 days' AND d.d), 0),
    2
  ) AS stickiness_pct
FROM dau d
WHERE d.d >= current_date - INTERVAL '90 days'
ORDER BY d.d;

Почему DAU нельзя посчитать просто COUNT(*) ... GROUP BY day

Один пользователь генерирует много событий — нужен DISTINCT user_id. Промежуточный шаг daily уже сделал дедуп, поэтому в dau можно писать COUNT(*).

MAU rolling без коррелированного подзапроса

В Postgres 14+ можно так:

SELECT
  d,
  COUNT(DISTINCT user_id) OVER (
    ORDER BY d
    RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
  ) AS mau_30d
FROM daily;

Не все СУБД поддерживают COUNT(DISTINCT ...) OVER (RANGE ...), и это часто медленно. На больших данных лучше прекомпьютить.

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

1. COUNT(DISTINCT user_id) OVER (...) в большинстве СУБД не работает или медленный. Часто заменяют коррелированным подзапросом, как выше.
2. MAU как «последние 30 дней» vs «календарный месяц» — два разных бизнес-определения. Уточняйте.
3. Stickiness считается по разным базам. Иногда AVG(DAU за месяц) / MAU (Facebook-определение), иногда DAU_today / MAU_today.
4. Фильтр >= now() - 120 days в WITH daily, чтобы хватило истории для MAU на самой ранней дате окна 90 дней.
5. Часовые пояса. DATE(event_time) зависит от TZ. Для бизнес-отчётов фиксируйте event_time AT TIME ZONE 'Europe/Moscow'.

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

DAU = COUNT(DISTINCT user_id) GROUP BY date. MAU rolling = COUNT(DISTINCT user_id) WHERE date BETWEEN d-29 AND d. Stickiness = DAU/MAU. На больших данных оконные функции с DISTINCT обычно заменяют коррелированным подзапросом или прекомпьютом.