Deviopath › PostgreSQL

7-Day Moving Average

Hard · Window Functions

A window frame limits which rows a window function sees. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means "this row and the 6 rows before it", which gives a 7-day moving average when there is one row per day.

For every row of daily_sales, return store, day, revenue and avg_7d: the average revenue of that store over the current row and the 6 rows before it (fewer at the start, when fewer rows exist), rounded to 2 decimals. Each store is averaged separately.

Sort by store, then day.

Next: Running Balance Goes Negative