-- Created on savesnippets.com · https://savesnippets.com/9uPxadBgrSCJzb
-- 7-day moving average of daily signups
SELECT day,
       signups,
       AVG(signups) OVER (
         ORDER     BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS ma_7d
FROM   daily_signups
ORDER  BY day;

-- Centered 5-day moving average (2 before, 2 after)
SELECT day,
       revenue,
       AVG(revenue) OVER (
         ORDER     BY day
         ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
       ) AS ma_5d_centered
FROM   daily_revenue;

-- Time-based frame (PostgreSQL) — last 7 days regardless of row count
SELECT ts, value,
       AVG(value) OVER (
         ORDER     BY ts
         RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
       ) AS rolling_7d_avg
FROM   metrics;