SQL: Посчитать DAU и WAU

SQL: Посчитать DAU и WAU

Объяснение метрик

DAU (Daily Active Users) — количество уникальных пользователей, выполнивших хотя бы одно действие в течение дня. Это базовая метрика для оценки ежедневной активности сервиса.

WAU (Weekly Active Users) — количество уникальных пользователей, выполнивших хотя бы одно действие на протяжении последних 7 дней (включая текущий день). Эта метрика показывает масштаб аудитории с недельным горизонтом и менее подвержена ежедневным колебаниям.

Решение с оконными функциями

SELECT
    activity_date as date,
    COUNT(DISTINCT user_id) as dau,
    COUNT(DISTINCT user_id) OVER (
        ORDER BY activity_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as wau
FROM user_activity
GROUP BY activity_date
ORDER BY activity_date;

Как работает:

• Сначала группируем по дате и считаем уникальных пользователей для DAU
• Для WAU используем оконную функцию OVER с рамкой окна: смотрим на текущую строку и 6 предыдущих дней (итого 7 дней)
• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW определяет скользящее окно размером 7 дней

Альтернативное решение (для разных SQL диалектов)

Если оконные функции работают нестабильно, можно использовать подзапрос:

WITH daily_users AS (
    SELECT
        activity_date,
        COUNT(DISTINCT user_id) as dau
    FROM user_activity
    GROUP BY activity_date
)
SELECT
    d1.activity_date as date,
    d1.dau,
    COUNT(DISTINCT d2.user_id) as wau
FROM daily_users d1
CROSS JOIN user_activity d2
WHERE d2.activity_date BETWEEN d1.activity_date - INTERVAL 6 DAY AND d1.activity_date
GROUP BY d1.activity_date, d1.dau
ORDER BY d1.activity_date;

Этот подход работает на большинстве платформ (MySQL, PostgreSQL, SQL Server), но медленнее на больших объёмах данных.

Оптимизация для production

Для высоконагруженных систем рекомендуется:

• Добавить индекс по (activity_date, user_id)
• Материализовать результат в отдельную таблицу если это часто используемая метрика
• Использовать партиционирование по датам для больших таблиц