Как посчитать процентное изменение выручки год к году с учётом сезонности, сравнивая не соседние месяцы, а тот же месяц прошлого года?

Короткий ответ

Нужно использовать LAG(revenue, 12) OVER (ORDER BY month) вместо LAG(revenue, 1, ...), чтобы сравнивать каждый месяц не с предыдущим, а с тем же месяцем год назад — это сравнение год-к-году (YoY) корректно учитывает сезонность.

Как это работает подробнее

параметр n в LAG(column, n) задаёт, на сколько строк назад по заданному порядку смотреть; если ряд строго помесячный без пропусков, n = 12 сдвигает на 12 месяцев назад, то есть на тот же календарный месяц предыдущего года.

Пример кода

question-61.sqlКопировать

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
             / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 1) AS yoy_pct
FROM monthly ORDER BY month;

Что использовать на практике

• Практика. сравнение месяц-к-месяцу (MoM) часто вводит в заблуждение для сезонных продуктов (например, продажи ёлочных игрушек в январе логично падают относительно декабря — это не признак проблем с продуктом), поэтому для таких бизнесов сравнение YoY даёт более содержательную картину. Важно убедиться, что ряд действительно непрерывен (нет пропущенных месяцев), иначе LAG(..., 12) «сдвинется» не на 12 календарных месяцев, а на 12 существующих строк, что не одно и то же при пропусках.
• Проверка. Проверка: для конкретного месяца вручную найти выручку этого же месяца год назад в исходных данных и сравнить с результатом LAG.

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

• Частая ошибка. применяют LAG(revenue, 12) к ряду с пропущенными месяцами, получая сравнение с неверным историческим периодом без явной ошибки выполнения — запрос «работает», но результат содержательно неверен; не учитывают, что длина месяца и число рабочих/праздничных дней могут отличаться год к году, что тоже влияет на сравнение.