Условие

Дана таблица signups(user_id, signup_date). Напишите запрос, который для каждого дня показывает 7-дневное скользящее среднее регистраций (текущий день и 6 предыдущих).

Решение

Подход

Сначала агрегируем число регистраций по дню, потом применяем оконное AVG с фреймом ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Если в данных могут быть пропущенные даты (день без регистраций), используйте RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW или предварительный LEFT JOIN на серию дат.

Реализация

WITH daily AS (
    SELECT
        signup_date::date AS d,
        COUNT(*)          AS n_signups
    FROM signups
    GROUP BY 1
)
SELECT
    d,
    n_signups,
    AVG(n_signups) OVER (
        ORDER BY d
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d
FROM daily
ORDER BY d;

Если в данных есть пропущенные дни и нужно усреднять «по календарю»:

WITH days AS (
    SELECT generate_series(
        (SELECT MIN(signup_date)::date FROM signups),
        (SELECT MAX(signup_date)::date FROM signups),
        '1 day'
    )::date AS d
),
daily AS (
    SELECT d, COALESCE(COUNT(s.user_id), 0) AS n
    FROM days
    LEFT JOIN signups s ON s.signup_date::date = days.d
    GROUP BY d
)
SELECT d, n,
       AVG(n) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily;

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

ROWS BETWEEN 6 PRECEDING означает «6 строк», но если строк меньше (первые 6 дней — выборка неполная), окно всё равно посчитает среднее по тому, что есть. Это даёт «warmup-effect»: первые точки не сопоставимы с остальными.
На пропущенных датах ROWS посчитает 7 имеющихся, а календарно может пройти 14 дней. Используйте RANGE с интервалом или дозаполняйте серию дат.
Часовой пояс: signup_date может быть timestamptz, и ::date даст разные результаты в зависимости от tz сессии.

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

Агрегируйте по дню, потом AVG() OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). На пропущенных датах — дозаполняйте серию дней через generate_series.

Zachary Thomas: скользящее среднее sign-ups за 7 дней