Как посчитать active days в SQL

Зачем аналитику считать active days в SQL

Stickiness (DAU/MAU) даёт одно число — и часто обманывает. Active days per user — богаче сигнал: сколько дней из 28 пользователь реально был в продукте. У одного продукта DAU/MAU 20%, но это одни и те же power-users 28 дней из 28. У другого — разные люди с L1 = 20%. Цифра одинаковая, продукты совсем разные.

Active days — основа L-метрик (L28, L21, L7, L1), которые популяризировала Facebook. Их используют, чтобы увидеть «стики» ядро пользователей и streak-активности. Для этого нужны SQL-запросы, корректно учитывающие уникальные дни и распределение.

В статье — готовые SQL-шаблоны:

• Active days на пользователя за период
• Распределение (сколько пользователей активны N дней)
• L28 / L21 / L14 / L7 / L1 метрики
• Active days по когортам и сегментам
• Streak (количество дней активности подряд)
• Связь active days с monetization

Схема: events(user_id, event_at).

Что такое active days

Active days — количество дней, в которые пользователь проявлял активность в продукте.

Подходит как метрика engagement: сколько из 28 возможных дней пользователь был с продуктом. Часто используется вместо stickiness для более богатого сигнала.

Схема данных

events (user_id, event_at)

1. Active days пользователя за период

SELECT
    user_id,
    COUNT(DISTINCT DATE(event_at)) AS active_days
FROM events
WHERE event_at >= '2026-04-01'
  AND event_at <  '2026-05-01'
GROUP BY user_id;

2. Распределение active days

WITH user_days AS (
    SELECT
        user_id,
        COUNT(DISTINCT DATE(event_at)) AS active_days
    FROM events
    WHERE event_at >= NOW() - INTERVAL '28 days'
    GROUP BY user_id
)
SELECT
    active_days,
    COUNT(*) AS users_cnt
FROM user_days
GROUP BY active_days
ORDER BY active_days;

Показывает, сколько пользователей активны 1, 2, 3, ... 28 дней из 28.

3. L28 метрика (power users)

Доля пользователей, активных в каждый день из последних 28:

WITH user_days AS (
    SELECT
        user_id,
        COUNT(DISTINCT DATE(event_at)) AS days_active_28
    FROM events
    WHERE event_at >= NOW() - INTERVAL '28 days'
    GROUP BY user_id
)
SELECT
    AVG(CASE WHEN days_active_28 >= 1  THEN 1.0 ELSE 0 END) AS l1_rate,
    AVG(CASE WHEN days_active_28 >= 7  THEN 1.0 ELSE 0 END) AS l7_rate,
    AVG(CASE WHEN days_active_28 >= 14 THEN 1.0 ELSE 0 END) AS l14_rate,
    AVG(CASE WHEN days_active_28 >= 21 THEN 1.0 ELSE 0 END) AS l21_rate,
    AVG(CASE WHEN days_active_28 >= 28 THEN 1.0 ELSE 0 END) AS l28_rate
FROM user_days;

4. Active days за последние 7 / 14 / 28 дней

SELECT
    user_id,
    COUNT(DISTINCT CASE WHEN event_at >= NOW() - INTERVAL '7 days'  THEN DATE(event_at) END) AS days_7,
    COUNT(DISTINCT CASE WHEN event_at >= NOW() - INTERVAL '14 days' THEN DATE(event_at) END) AS days_14,
    COUNT(DISTINCT CASE WHEN event_at >= NOW() - INTERVAL '28 days' THEN DATE(event_at) END) AS days_28
FROM events
WHERE event_at >= NOW() - INTERVAL '28 days'
GROUP BY user_id;

5. Active days по когортам

Как active days меняется с возрастом пользователя:

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
    FROM events GROUP BY user_id
),
activity AS (
    SELECT
        user_id,
        DATE(event_at) AS active_date,
        DATE_TRUNC('month', event_at) AS activity_month
    FROM events
    GROUP BY user_id, DATE(event_at), DATE_TRUNC('month', event_at)
)
SELECT
    c.cohort_month,
    a.activity_month,
    -- приводим к NUMERIC, иначе bigint / bigint даст целочисленное деление
    COUNT(DISTINCT a.active_date)::NUMERIC / NULLIF(COUNT(DISTINCT c.user_id), 0) AS avg_active_days
FROM cohorts c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, a.activity_month
ORDER BY c.cohort_month, a.activity_month;

6. Самая длинная streak активности

Streak — последовательные дни активности.

WITH user_days AS (
    SELECT DISTINCT
        user_id,
        DATE(event_at) AS day
    FROM events
),
day_rn AS (
    SELECT
        user_id,
        day,
        day - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) * INTERVAL '1 day' AS streak_group
    FROM user_days
),
streaks AS (
    SELECT
        user_id,
        streak_group,
        COUNT(*) AS streak_length,
        MIN(day) AS streak_start,
        MAX(day) AS streak_end
    FROM day_rn
    GROUP BY user_id, streak_group
)
SELECT
    user_id,
    MAX(streak_length) AS longest_streak
FROM streaks
GROUP BY user_id;

Идея: если дни идут подряд, то day - rn_days = одинаковое значение → группа.

7. Текущий streak (до сегодня)

WITH user_days AS (
    SELECT DISTINCT
        user_id,
        DATE(event_at) AS day
    FROM events
    WHERE event_at >= NOW() - INTERVAL '60 days'
),
recent AS (
    SELECT
        user_id,
        day,
        CURRENT_DATE - day AS days_ago
    FROM user_days
),
continuous AS (
    SELECT
        user_id,
        day,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day DESC) - 1 AS expected_days_ago,
        (CURRENT_DATE - day) AS actual_days_ago
    FROM recent
)
SELECT
    user_id,
    COUNT(*) AS current_streak
FROM continuous
WHERE expected_days_ago = actual_days_ago
GROUP BY user_id;

8. Average active days per user

WITH user_days AS (
    SELECT user_id, COUNT(DISTINCT DATE(event_at)) AS active_days
    FROM events
    WHERE event_at >= NOW() - INTERVAL '28 days'
    GROUP BY user_id
)
SELECT
    AVG(active_days) AS avg_active_days_per_user,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY active_days) AS median
FROM user_days;

9. Segments by active days

Разделим пользователей на группы:

WITH user_days AS (
    SELECT user_id, COUNT(DISTINCT DATE(event_at)) AS active_days
    FROM events WHERE event_at >= NOW() - INTERVAL '28 days'
    GROUP BY user_id
)
SELECT
    CASE
        WHEN active_days >= 28 THEN 'super_user (L28)'
        WHEN active_days >= 21 THEN 'power_user'
        WHEN active_days >= 14 THEN 'regular'
        WHEN active_days >= 7  THEN 'casual'
        WHEN active_days >= 1  THEN 'occasional'
    END AS segment,
    COUNT(*) AS users_cnt
FROM user_days
GROUP BY 1;

10. Active days и monetization

Связь active days с тем, платит ли пользователь:

WITH user_activity AS (
    SELECT
        user_id,
        COUNT(DISTINCT DATE(event_at)) AS active_days
    FROM events WHERE event_at >= NOW() - INTERVAL '28 days'
    GROUP BY user_id
),
user_revenue AS (
    SELECT user_id, SUM(total) AS revenue
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
)
SELECT
    ua.active_days,
    COUNT(*) AS users,
    AVG(COALESCE(ur.revenue, 0)) AS avg_revenue_per_user,
    AVG(CASE WHEN ur.revenue > 0 THEN 1.0 ELSE 0 END) AS paid_conversion
FROM user_activity ua
LEFT JOIN user_revenue ur ON ur.user_id = ua.user_id
GROUP BY ua.active_days
ORDER BY ua.active_days;

Классика: больше active days → выше conversion и revenue.

Частые ошибки

Ошибка 1. COUNT без DISTINCT

-- считает события, не дни
COUNT(DATE(event_at))

-- правильно
COUNT(DISTINCT DATE(event_at))

Ошибка 2. Таймзоны

-- если сервер UTC, а юзер в МСК — дни могут сдвинуться
COUNT(DISTINCT DATE(event_at))

-- правильно
COUNT(DISTINCT DATE(event_at AT TIME ZONE 'Europe/Moscow'))

Ошибка 3. Брать события без фильтра

Бот-активности / тестовые события искажают. Фильтруйте перед агрегацией.

Ошибка 4. Смешивать окна

28 дней, 4 недели, месяц — все разные. Документируйте, что берёте.

Связанные темы

• Как считать DAU в SQL
• Как считать MAU в SQL
• Как посчитать stickiness в SQL
• Stickiness DAU/MAU

FAQ

Active days или DAU — что смотреть?

DAU — общая активность. Active days per user — глубина вовлечения. Оба полезны.

L28 = 100% — это идеал?

Идеал недостижим в большинстве продуктов. L28 даже 10% — хорошо для большинства B2C.

Streak — это активность подряд?

Да. Каждый день без пропуска. После пропуска — счётчик обнуляется.

Как часто пересчитывать active days?

Ежедневно (batch) или по запросу. Для дашбордов — ежедневно через materialized view.