Window Frames: ROWS and RANGE

Window frames let you define exactly which rows participate in each calculation. Need a 7-day moving average? A running total? Sum of previous 3 rows? Frames give you precise control.

The Frame Clause Syntax

function(...) OVER (
  PARTITION BY ...
  ORDER BY ...
  frame_type BETWEEN frame_start AND frame_end
)

Default Frame Behavior

ROWS vs RANGE

ROWS: Physical Row Count

ROWS counts actual rows regardless of values.

SELECT
  date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as sum_3_rows
FROM daily_sales;

RANGE: Value-Based Boundaries

RANGE uses the ORDER BY column's values, not row count.

-- Include all rows with the same date (handles ties)
SELECT
  date,
  sale_id,
  amount,
  SUM(amount) OVER (
    ORDER BY date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM sales;

Notice: Both Jan 1 rows show 250 because RANGE includes all rows with the same date value.

Moving Averages

7-Day Moving Average

SELECT
  date,
  revenue,
  ROUND(AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) as avg_7_day
FROM daily_sales;

Centered Moving Average

-- 5-day centered average (2 before, current, 2 after)
SELECT
  date,
  value,
  AVG(value) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  ) as centered_avg
FROM timeseries;

Common Frame Patterns

Real-World: Stock Price Analysis

SELECT
  date,
  close_price,
  -- 20-day moving average
  ROUND(AVG(close_price) OVER (
    ORDER BY date
    ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
  ), 2) as ma_20,
  -- 50-day moving average
  ROUND(AVG(close_price) OVER (
    ORDER BY date
    ROWS BETWEEN 49 PRECEDING AND CURRENT ROW
  ), 2) as ma_50,
  -- Highest in last 52 weeks
  MAX(close_price) OVER (
    ORDER BY date
    ROWS BETWEEN 251 PRECEDING AND CURRENT ROW
  ) as high_52w,
  -- Lowest in last 52 weeks
  MIN(close_price) OVER (
    ORDER BY date
    ROWS BETWEEN 251 PRECEDING AND CURRENT ROW
  ) as low_52w
FROM stock_prices;

Real-World: Cumulative Distribution

-- What percentage of orders are below each value?
SELECT
  order_value,
  COUNT(*) OVER (ORDER BY order_value) as orders_at_or_below,
  COUNT(*) OVER () as total_orders,
  ROUND(100.0 * COUNT(*) OVER (ORDER BY order_value) / COUNT(*) OVER (), 2) as percentile
FROM orders;

EXCLUDE Clause (PostgreSQL 11+)

Fine-tune what's excluded from the frame:

-- Average excluding the current row
AVG(value) OVER (
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  EXCLUDE CURRENT ROW
)

Options:

• EXCLUDE CURRENT ROW - Exclude current row
• EXCLUDE GROUP - Exclude current row and peers
• EXCLUDE TIES - Exclude peers but keep current row
• EXCLUDE NO OTHERS - Default, exclude nothing

Key Takeaways

Next up: Window Functions Assessment - Test your knowledge with practical scenarios.