StrataScratch (Amazon) — Monthly Percentage Difference: MoM revenue %

Условие

Таблица sf_transactions(id, created_at, value, purchase_id) — транзакции. Посчитайте процентное изменение выручки месяц к месяцу.

Выход: year_month (формат YYYY-MM), revenue_diff_pct (округление до 2 знаков). Для первого месяца — NULL. Сортировка по year_month.

Формула: ((this_month_rev - prev_month_rev) / prev_month_rev) * 100.

Решение

Шаг 1 — агрегация до месяца

WITH monthly AS (
  SELECT
    TO_CHAR(created_at, 'YYYY-MM') AS year_month,
    SUM(value) AS revenue
  FROM sf_transactions
  GROUP BY 1
),

Шаг 2 — LAG по месяцам

diff AS (
  SELECT
    year_month,
    revenue,
    LAG(revenue) OVER (ORDER BY year_month) AS prev_revenue
  FROM monthly
)
SELECT
  year_month,
  ROUND(
    ((revenue - prev_revenue)::DECIMAL / NULLIF(prev_revenue, 0)) * 100,
    2
  ) AS revenue_diff_pct
FROM diff
ORDER BY year_month;

Почему агрегация до LAG

Если делать LAG(value) OVER (ORDER BY created_at) сразу — получим разницу между двумя соседними транзакциями, а не между месячными итогами. Это классический подвох. Сначала агрегируем по месяцу, потом LAG.

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

1. Целочисленное деление. (revenue - prev) в int даст обрезанный 0%. ::DECIMAL или 1.0 * обязательно.
2. prev_revenue = 0. NULLIF(prev_revenue, 0) — деление на NULL даёт NULL, а не ошибку.
3. Месяцы-пропуски. Если выручка была только в Янв и Мар — LAG сравнит Мар с Янв. Бизнесово — неверно. Нужен calendar table и LEFT JOIN.
4. Сортировка лексикографическая. TO_CHAR(... 'YYYY-MM') сортируется как строка, и это совпадает с хронологией только потому, что год идёт первым. Для безопасности можно ORDER BY DATE_TRUNC.

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

Двухшаговая агрегация: сначала SUM(value) GROUP BY YYYY-MM, потом LAG ... ORDER BY year_month, потом формула с NULLIF и ::DECIMAL.