Tutorial: SQL window functions for rolling metrics

Rolling metrics are useful, but the window must match the business question. For daily revenue, a 7-row window is not necessarily seven calendar days if dates are missing. Build a complete date spine first, then join facts and use a defined window.

Code:

SUM(revenue) OVER (PARTITION BY product ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Check how nulls, returns, timezone boundaries, and late-arriving data affect the result. Test the query on a small hand-calculated sample before adding it to a dashboard.

Which rolling metric do you use most often?