Part 97: Window Frames: ROWS BETWEEN vs RANGE BETWEEN

The window frame clause defines the exact subset of rows within the partition used for the calculation.

1. The Frame Syntax

-- Moving Average of the current row and 2 preceding rows (3-day moving average):
SELECT 
  sale_date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY sale_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rolling_3day_avg
FROM daily_sales;

• ROWS: Operates on physical row counts.
• RANGE: Operates on logical value differences.

Key Takeaways

• Explicitly define window frames to calculate rolling metrics accurately.

In Part 98, we will calculate Cumulative Running Totals and Moving Averages!