Frame Specifications

Concepts covered: Window Frames

A frame is the slice of a partition that an aggregate window function reads for one row. The clause starts with ROWS or RANGE to pick the unit, then gives a start and an end. Each bound is either CURRENT ROW or a distance in one direction, n PRECEDING or n FOLLOWING, with UNBOUNDED in place of n to reach the edge of the partition. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING covers 3 rows, the current row plus 1 on each side. The frame slides along with the current row, so each row's average is taken over its own neighbours rather than over a fixed block.

SELECT
  sale_date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg_3
FROM daily_revenue

The frame shrinks at the edges rather than failing. 2025-03-01 has no row before it, so its frame holds 2 rows and the average is 460, (400 + 520) / 2. 2025-03-03 sits in the middle, with 520 before it and 650 after, and its own 310 averages with them to 493.3. 2025-03-05 averages its own 480 with the 650 before it, giving 565. A 3-day average that covers 2 days at the ends is right for a chart and wrong for a report that promised 3 full days, and a COUNT(*) over the same frame tells the reader how many days each figure covers.

SELECT
  sale_date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS trailing_3_sum,
  COUNT(*) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS days_in_frame
FROM daily_revenue

This frame trails: it ends at the current row and reaches 2 rows back, so the sums run 400, 920, 1230, 1480, 1440. days_in_frame climbs from 1 to 3 over the first 3 days and stays at 3 after that. A trailing window is the shape of every rolling metric that must never peek at the future, which is the rule for anything feeding a forecast. Filtering to days_in_frame = 3 in an outer query drops the 2 partial days.

ROWS counts rows, RANGE counts values

ROWS measures the frame in physical rows. RANGE measures it in values of the ORDER BY column: rows that share the same sort value are peers, and a RANGE frame ending at CURRENT ROW includes every peer of the current row, even the ones placed after it. The difference only shows when the sort column has duplicates, which real data has constantly: 2 orders on the same day, 2 tracks with the same stream count.

ROWS

Each row gets its own running total, and the 2 day-2 orders receive different totals in an order the engine chooses.

SELECT
  order_day,
  amount,
  SUM(amount) OVER (
    ORDER BY order_day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders

RANGE

The 2 day-2 orders are peers, so both frames include both rows and both report 350.

SELECT
  order_day,
  amount,
  SUM(amount) OVER (
    ORDER BY order_day
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders

Under ROWS, the 2 day-2 orders get different running totals. The one the engine sorts first reads 300 or 150, depending on which order that is, and the other reads 350, so a second run can swap them. Under RANGE both read 350, because both share the value 2 in order_day and each frame includes its peer. A running total by day should be identical for every order on that day, and RANGE delivers that. RANGE is also the default unit when OVER has an ORDER BY and no frame.

OVER with no ORDER BY

• The frame is the whole partition
• SUM returns the partition total on every row
• FIRST_VALUE and LAST_VALUE see every row
• Row order inside the partition carries no meaning

OVER with ORDER BY, no frame written

• The frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• SUM returns a running total
• LAST_VALUE sees up to the current row and its peers
• Tied rows share one frame and one result

Do

• Write ROWS when the frame is a count of rows, such as the last 7 or the surrounding 3
• Pair a sliding average with a COUNT(*) over the same frame so partial edges are visible
• Spell the frame out whenever the function must see rows after the current one

Avoid

• Rely on ROWS with a tied ORDER BY column; the split between ties changes between runs
• Assume an ordered window covers the whole partition; it stops at the current row
• Write n PRECEDING with RANGE when you mean rows; RANGE measures the sort value

On the 5-row daily_revenue table, what does SUM(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) return for 2025-03-01?

• 920, the sum of the current row and the next
• 1230, the sum of the first 3 rows of the table
• 830, the sum of the 2 rows after the current one
• 960, the sum of the rows 2 and 3 places ahead

About this lesson

This section is part of the Window Functions: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.