Процентное изменение MAU месяц к месяцу

Решение

Задача требует расчёта ежемесячного процентного изменения MAU (Monthly Active Users). Это ключевая метрика для отслеживания роста пользовательской базы.

Подход к решению

Логика:

1. Вычислить MAU для каждого месяца (количество уникальных пользователей)
2. Получить MAU из предыдущего месяца с помощью LAG()
3. Рассчитать процентное изменение: ((текущий - предыдущий) / предыдущий) * 100
4. Для первого месяца результат будет NULL

SQL-запрос

WITH monthly_mau AS (
  SELECT 
    DATE_TRUNC('month', date) AS month,
    COUNT(DISTINCT user_id) AS mau
  FROM logins
  GROUP BY DATE_TRUNC('month', date)
)
SELECT 
  TO_CHAR(month, 'YYYY-MM') AS month,
  mau,
  ROUND(
    (
      (mau - LAG(mau) OVER (ORDER BY month)) / 
      LAG(mau) OVER (ORDER BY month) * 100
    )::NUMERIC,
    2
  ) AS mau_change_pct
FROM monthly_mau
ORDER BY month ASC;

Пошаговое объяснение

1. CTE monthly_mau

• DATE_TRUNC('month', date) — приводит все даты месяца к первому дню
• COUNT(DISTINCT user_id) — подсчитывает уникальных пользователей в месяц
• GROUP BY — группируем по месяцам

2. LAG() оконная функция

• LAG(mau) OVER (ORDER BY month) — получает MAU из предыдущей строки
• ORDER BY month — упорядочиваем по времени
• Для первого месяца возвращает NULL

3. Формула процентного изменения

• (mau - LAG(mau)) / LAG(mau) * 100
• Числитель: разница между текущим и предыдущим MAU
• Знаменатель: MAU предыдущего месяца (базовое значение)
• Результат умножаем на 100 для получения процентов

4. Форматирование месяца

• TO_CHAR(month, 'YYYY-MM') — выводим месяц в формате "2024-01"

Вариант для MySQL

WITH monthly_mau AS (
  SELECT 
    DATE_FORMAT(date, '%Y-%m') AS month,
    COUNT(DISTINCT user_id) AS mau
  FROM logins
  GROUP BY DATE_FORMAT(date, '%Y-%m')
),
mau_with_lag AS (
  SELECT 
    month,
    mau,
    LAG(mau) OVER (ORDER BY month) AS prev_mau
  FROM monthly_mau
)
SELECT 
  month,
  mau,
  ROUND(
    ((mau - prev_mau) / prev_mau * 100),
    2
  ) AS mau_change_pct
FROM mau_with_lag
ORDER BY month;

Альтернативный вариант (более компактный)

SELECT 
  TO_CHAR(month, 'YYYY-MM') AS month,
  mau,
  ROUND(
    (
      (mau::NUMERIC - LAG(mau) OVER (ORDER BY month)) / 
      LAG(mau) OVER (ORDER BY month) * 100
    ),
    2
  ) AS mau_change_pct
FROM (
  SELECT 
    DATE_TRUNC('month', date) AS month,
    COUNT(DISTINCT user_id) AS mau
  FROM logins
  GROUP BY DATE_TRUNC('month', date)
) monthly_stats
ORDER BY month;

Пример выполнения

Исходные данные:

user_id | date
--------|------------
1       | 2024-01-05
2       | 2024-01-15
3       | 2024-01-20
1       | 2024-02-10
4       | 2024-02-15

Этап 1: Расчёт MAU по месяцам

month    | mau
---------|----
2024-01  | 3  (пользователи 1, 2, 3)
2024-02  | 2  (пользователи 1, 4)

Этап 2: LAG для получения предыдущего MAU

month    | mau | prev_mau
---------|-----|----------
2024-01  | 3   | NULL
2024-02  | 2   | 3

Этап 3: Расчёт процентного изменения

Для 2024-01: (3 - NULL) / NULL * 100 = NULL
Для 2024-02: (2 - 3) / 3 * 100 = -1 / 3 * 100 = -33.33%

Финальный результат:

month    | mau | mau_change_pct
---------|-----|---------------
2024-01  | 3   | NULL
2024-02  | 2   | -33.33

Расширенный вариант с анализом

WITH monthly_mau AS (
  SELECT 
    DATE_TRUNC('month', date) AS month,
    COUNT(DISTINCT user_id) AS mau
  FROM logins
  GROUP BY DATE_TRUNC('month', date)
),
mau_trends AS (
  SELECT 
    TO_CHAR(month, 'YYYY-MM') AS month,
    mau,
    LAG(mau) OVER (ORDER BY month) AS prev_mau,
    mau - LAG(mau) OVER (ORDER BY month) AS mau_absolute_change,
    ROUND(
      (
        (mau::NUMERIC - LAG(mau) OVER (ORDER BY month)) / 
        LAG(mau) OVER (ORDER BY month) * 100
      ),
      2
    ) AS mau_change_pct,
    CASE 
      WHEN (mau::NUMERIC - LAG(mau) OVER (ORDER BY month)) / LAG(mau) OVER (ORDER BY month) * 100 > 0 THEN 'Growth'
      WHEN (mau::NUMERIC - LAG(mau) OVER (ORDER BY month)) / LAG(mau) OVER (ORDER BY month) * 100 < 0 THEN 'Decline'
      ELSE 'Flat'
    END AS trend
  FROM monthly_mau
)
SELECT 
  month,
  mau,
  mau_absolute_change,
  mau_change_pct,
  trend
FROM mau_trends
ORDER BY month;

Это добавляет абсолютное изменение и категорию тренда (рост/падение).

Интерпретация результатов

Положительное значение — рост аудитории

• 50.00% → увеличение на 50%
• Формула: ((новое - старое) / старое) * 100

Отрицательное значение — падение аудитории

• -33.33% → уменьшение на 33.33%

NULL — первый месяц (нет предыдущего значения для сравнения)

Важные моменты

✓ DISTINCT в COUNT — критично для получения уникальных пользователей

✓ LAG() — оконная функция для доступа к предыдущей строке

✓ ORDER BY в LAG — определяет порядок, в котором берутся предыдущие значения

✓ ::NUMERIC в PostgreSQL — необходим для корректного деления

✓ NULL для первого месяца — нормальное поведение при отсутствии предыдущего значения

✓ DATE_TRUNC vs DATE_FORMAT — используй в зависимости от СУБД

✓ Анализ роста — положительный процент указывает на здоровый рост, отрицательный требует анализа причин