Условие

Есть таблица orders(order_id, user_id, created_at, amount, status). Для каждого дня посчитайте:

Выручку за день (gmv).
Кумулятивную выручку с начала месяца (running total within month).
7-дневное скользящее среднее (центрированное в правом крае окна).
Долю дневной выручки в месячной (share of month).

Решение

Подход

Сначала агрегируем до дневной grain, потом оконные функции с правильным PARTITION BY date_trunc('month', dt).

WITH daily AS (
    SELECT created_at::date AS dt,
           SUM(amount) FILTER (WHERE status = 'paid') AS gmv
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY 1
),
agg AS (
    SELECT
        dt,
        gmv,
        date_trunc('month', dt)::date AS month_start,
        SUM(gmv) OVER (
            PARTITION BY date_trunc('month', dt)
            ORDER BY dt
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS gmv_cum_month,
        AVG(gmv) OVER (
            ORDER BY dt
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS gmv_ma7,
        SUM(gmv) OVER (
            PARTITION BY date_trunc('month', dt)
        ) AS gmv_month_total
    FROM daily
)
SELECT
    dt,
    gmv,
    gmv_cum_month,
    gmv_ma7,
    ROUND(gmv * 100.0 / NULLIF(gmv_month_total, 0), 2) AS pct_of_month
FROM agg
ORDER BY dt;

Разбор

PARTITION BY date_trunc('month', dt) — running total сбрасывается в начале каждого месяца.
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — окно из 7 дней (включая текущий). Скользящее MA7.
SUM(gmv) OVER (PARTITION BY month) без ORDER BY — итог по месяцу для каждой строки → используем для share.
NULLIF(..., 0) страхует от деления на ноль в пустых месяцах.

Альтернатива через self-join

SELECT a.dt, a.gmv,
       (SELECT SUM(b.gmv) FROM daily b
        WHERE date_trunc('month', b.dt) = date_trunc('month', a.dt)
          AND b.dt <= a.dt) AS gmv_cum_month
FROM daily a;

Работает, но O(n²) на больших таблицах. Window-функции — O(n log n).

MA7 при пропусках дат

Если в daily пропуски (нет заказов за выходные), ROWS BETWEEN 6 PRECEDING возьмёт 7 предыдущих строк, а не 7 календарных дней. Нужно сначала развернуть на полный календарь:

WITH cal AS (
    SELECT generate_series(date '2025-01-01', date '2025-12-31', INTERVAL '1 day')::date AS dt
)
SELECT c.dt, COALESCE(d.gmv, 0) AS gmv
FROM cal c LEFT JOIN daily d USING (dt)

— и уже над этим строить MA7.

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

ROWS vs RANGE: ROWS BETWEEN 6 PRECEDING = 7 физических строк; RANGE BETWEEN '6 day' PRECEDING = 7 дней по значению dt. Для пропусков RANGE корректнее (Postgres / Snowflake).
PARTITION BY date_trunc('month', dt) — обязательно, иначе running total пойдёт на весь горизонт.
NULLIF для деления — без него pct_of_month = NULL или ошибка деления на 0.
Часовые пояса: created_at::date берёт время в сессионной TZ. Для агрегатов выручки явно created_at AT TIME ZONE 'Europe/Moscow'::date.
SUM(amount) FILTER vs SUM(CASE WHEN ...) — FILTER короче и поддерживается Postgres/Snowflake/BigQuery; в MySQL — SUM(CASE).
MA7 «центрированное» vs «правое»: правое (6 PRECEDING AND CURRENT) — отстаёт на 3 дня от текущего среднего; центрированное (3 PRECEDING AND 3 FOLLOWING) — нельзя считать на сегодня, потому что нужны будущие данные.
ORDER BY dt в окне — без него UNBOUNDED PRECEDING сложится по всему month_set, не по нарастающей.

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

SUM(gmv) OVER (PARTITION BY date_trunc('month', dt) ORDER BY dt
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS gmv_cum_month,
AVG(gmv) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS gmv_ma7,
gmv / SUM(gmv) OVER (PARTITION BY date_trunc('month', dt)) * 100 AS pct_of_month

Ключи: PARTITION BY date_trunc('month', dt) для running total в пределах месяца; ROWS BETWEEN 6 PRECEDING AND CURRENT ROW для MA7; календарь дополнить через generate_series при пропусках.

DataLearn DE-101: Кумулятивная выручка по дням — оконные функции