Lesson 2 - Window Frames: ROWS and RANGE

Welcome to Window Framing#

In Lesson 1 you learned how PARTITION BY divides a window into groups and how ORDER BY sets the order of rows inside it. There is one more level of control: the window frame. A frame lets you specify a window smaller than the partition — for example, the rows from the start of the partition up to the current row, or a sliding band of three rows centered on the current row.

Framing is what makes running totals and moving averages precise. With a frame you say exactly which rows, relative to the current row, should be included when the function computes its value. This lesson shows you the two ways to define a frame and the difference between them.

By the end of this lesson, you will be able to:

• Explain what a window frame is and why it matters for accuracy
• Define frames with ROWS BETWEEN and RANGE BETWEEN
• Use the bound terms UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW, N PRECEDING, and N FOLLOWING
• Explain the difference between ROWS and RANGE and know when each applies

This lesson assumes you are comfortable with OVER(), PARTITION BY, and ORDER BY from Lesson 1. Let’s dive in.

Window Framing Syntax#

A window frame defines two endpoints relative to the current row within the partition. The function is then applied only to the rows that fall between those endpoints, in the order set by ORDER BY. Framing applies to aggregate window functions and to a few offset functions you will meet later.

The standard syntax offers two main options:

ROWS BETWEEN starting_bound AND ending_bound
RANGE BETWEEN starting_bound AND ending_bound

The starting bound can be one of:

The ending bound can be one of:

Framing lets you look at the neighborhood of each row. For instance, a frame of “one row before, the current row, and one row after” gives you a centered moving average. Using apple_sales_quantity_by_month extended to six months:

SELECT *,
       AVG(quantity) OVER(ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_quantity
  FROM apple_sales_quantity_by_month;

sales_date | brand | quantity | avg_quantity
-----------+-------+----------+-------------
2022-01-31 | Apple | 50       | 45.00
2022-02-28 | Apple | 40       | 38.33
2022-03-31 | Apple | 25       | 31.67
2022-04-30 | Apple | 30       | 34.00
2022-05-31 | Apple | 47       | 39.00
2022-06-30 | Apple | 40       | 43.50

The first row averages only itself and the next row (there is no preceding row), giving $(50 + 40) / 2 = 45$. The third row averages 40, 25, and 30, giving 31.67. Don’t worry if this feels dense — the next sections break it down.

Building a Running Total with a Frame#

The most common frame builds a running total (also called an accumulating aggregate). You want each row to include everything from the start of the partition up to and including the current row. That is exactly:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Here it is computing a running total of phone quantities:

SELECT *,
       SUM(quantity) OVER(ORDER BY sales_date
                          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_quantity
  FROM apple_sales_quantity_by_month;

The frame for row 1 includes row 1; for row 2 it includes rows 1 and 2; for row 3, rows 1 through 3, and so on. The window grows by one row each step. You could write the equivalent frame with ROWS:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

You can also build a reverse running total that accumulates from the current row to the end of the partition:

SELECT *,
       SUM(quantity) OVER(ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS reverse_running_total_quantity
  FROM apple_sales_quantity_by_month;

Now the first row sums everything, and the last row sums only itself.

The Default Frame Shortcut#

Writing the full frame every time is tedious, so SQL gives you sensible defaults:

• Without ORDER BY in OVER(), the default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — the entire partition.
• With ORDER BY in OVER(), the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — a running total.

That second default means you can compute a running total just by adding ORDER BY, with no explicit frame:

SELECT *,
       SUM(quantity) OVER(ORDER BY sales_date) AS running_total_quantity
  FROM apple_sales_quantity_by_month;

sales_date | brand | quantity | running_total_quantity
-----------+-------+----------+-----------------------
2022-01-31 | Apple | 50       | 50
2022-02-28 | Apple | 40       | 90
2022-03-31 | Apple | 25       | 115
2022-04-30 | Apple | 30       | 145
2022-05-31 | Apple | 47       | 192
2022-06-30 | Apple | 40       | 232

This is identical to the explicit RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW version. The same shortcut works for AVG(), giving a running average.

Framing with N PRECEDING and N FOLLOWING#

To look at a fixed number of neighboring rows, use N PRECEDING and N FOLLOWING. This syntax works only with ROWS. It lets you define a frame of so many rows before, after, or around the current row.

Consider phone_sales_quantity_by_month, which holds monthly quantities for two brands, Apple and Samsung. The query below sums the two preceding rows — the frame starts two rows before the current row and ends one row before it:

SELECT *,
       SUM(quantity) OVER(PARTITION BY brand
                          ORDER BY sales_date
                          ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS sum_two_preceding
  FROM phone_sales_quantity_by_month;

sales_date | brand   | quantity | sum_two_preceding
-----------+---------+----------+------------------
2022-01-31 | Apple   | 110      | (null)
2022-02-28 | Apple   | 60       | 110
2022-03-31 | Apple   | 85       | 170
2022-04-30 | Apple   | 134      | 145
2022-05-31 | Apple   | 90       | 219
2022-06-30 | Apple   | 100      | 224
2022-01-31 | Samsung | 117      | (null)
2022-02-28 | Samsung | 75       | 117
2022-03-31 | Samsung | 86       | 192
2022-04-30 | Samsung | 124      | 161
2022-05-31 | Samsung | 80       | 210
2022-06-30 | Samsung | 89       | 204

There are two partitions, Apple and Samsung. Notice how the result resets at the start of each brand.

• The first row of each partition has no preceding rows, so its frame is empty and the sum is NULL.
• The second row has only one preceding row, so its sum equals the first row’s quantity (110 for Apple, 117 for Samsung).
• From the third row on, the frame holds exactly the two previous rows. For Apple’s March, that is $110 + 60 = 170$.

To build a centered moving average, combine PRECEDING and FOLLOWING — for example, the previous row, the current row, and the next row:

SELECT *,
       AVG(revenue) OVER(PARTITION BY brand
                         ORDER BY sales_date
                         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS average_revenue
  FROM phone_sales_revenue_by_month;

This is the classic three-point moving average, computed independently within each brand.

The Difference Between ROWS and RANGE#

So far ROWS and RANGE may seem interchangeable for running totals. They are not. The difference shows up when the ORDER BY column has duplicate values.

• ROWS counts physical rows. A frame of ROWS UNBOUNDED PRECEDING includes every individual row up to and including the current one.
• RANGE works on values. A frame of RANGE UNBOUNDED PRECEDING includes every row whose ORDER BY value is less than or equal to the current row’s value — so rows that tie on the ordering column are treated as a single group.

Consider samsung_sales_quantity, where several models can sell on the same date:

SELECT *,
       SUM(quantity) OVER(ORDER BY sales_date
                          ROWS UNBOUNDED PRECEDING) AS cumulative_quantity_with_rows
  FROM samsung_sales_quantity;

sales_date | brand   | model                    | quantity | cumulative_quantity_with_rows
-----------+---------+--------------------------+----------+------------------------------
2022-01-31 | Samsung | Samsung Galaxy Z Fold4   | 40       | 40
2022-01-31 | Samsung | Samsung Galaxy S22 Ultra | 30       | 70
2022-02-28 | Samsung | Samsung Galaxy S22 Ultra | 35       | 105
2022-03-31 | Samsung | Samsung Galaxy S22 Ultra | 25       | 130
2022-03-31 | Samsung | Samsung Galaxy Z Fold4   | 60       | 190
2022-04-30 | Samsung | Samsung Galaxy Z Fold4   | 25       | 215
2022-05-31 | Samsung | Samsung Galaxy Z Fold4   | 30       | 245
2022-05-31 | Samsung | Samsung Galaxy S22 Ultra | 47       | 292
2022-06-30 | Samsung | Samsung Galaxy Z Fold4   | 76       | 368

With ROWS, the running total advances one row at a time, even within the same date. The two January rows get 40 and 70.

Now switch to RANGE:

SELECT *,
       SUM(quantity) OVER(ORDER BY sales_date
                          RANGE UNBOUNDED PRECEDING) AS cumulative_quantity_with_range
  FROM samsung_sales_quantity;

sales_date | brand   | model                    | quantity | cumulative_quantity_with_range
-----------+---------+--------------------------+----------+-------------------------------
2022-01-31 | Samsung | Samsung Galaxy Z Fold4   | 40       | 70
2022-01-31 | Samsung | Samsung Galaxy S22 Ultra | 30       | 70
2022-02-28 | Samsung | Samsung Galaxy S22 Ultra | 35       | 105
2022-03-31 | Samsung | Samsung Galaxy S22 Ultra | 25       | 190
2022-03-31 | Samsung | Samsung Galaxy Z Fold4   | 60       | 190
2022-04-30 | Samsung | Samsung Galaxy Z Fold4   | 25       | 215
2022-05-31 | Samsung | Samsung Galaxy Z Fold4   | 30       | 292
2022-05-31 | Samsung | Samsung Galaxy S22 Ultra | 47       | 292
2022-06-30 | Samsung | Samsung Galaxy Z Fold4   | 76       | 368

With RANGE, rows that share a sales_date share the same running total. Both January rows show 70, the total through the end of January. This is the right choice when you want the cumulative total per period rather than per physical row.

Practice Exercises#

Use the tables from this lesson: apple_sales_quantity_by_month, phone_sales_revenue_by_month, and samsung_sales_quantity.

Exercise 1: Running Average#

Return all columns of apple_sales_quantity_by_month plus a running average of quantity from the start of the partition to the current row, aliased as running_avg.

-- Your code here

Exercise 2: Average of All Previous Rows#

Using phone_sales_revenue_by_month, partition by brand, order by sales_date, and return the average revenue of all rows before the current row, aliased as avg_previous_revenues.

Exercise 3: Reverse Running Total with RANGE#

Using samsung_sales_quantity, order by sales_date and return all columns plus the total quantity of all sales from the current month and any later month, aliased as rev_run_quantity. Rows in the same month should share the total.

Summary#

You learned to control exactly which rows a window function sees. A window frame defines two endpoints relative to the current row, and the function applies only to the rows in between. ROWS counts physical rows; RANGE groups rows that tie on the ORDER BY value. With the right bound terms you can build running totals, reverse running totals, and moving averages with precision.

Key Concepts#

• Window frame — the band of rows, relative to the current row, that a window function includes in its calculation.
• ROWS BETWEEN — frames by physical row count; the only option that supports N PRECEDING and N FOLLOWING.
• RANGE BETWEEN — frames by value; rows with equal ORDER BY values are treated as one group.
• Bound terms — UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW, N PRECEDING, N FOLLOWING.
• Default frame — with ORDER BY present, OVER() defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is why adding ORDER BY alone produces a running total.

Why This Matters#

Framing is the difference between a running total and a 7-day moving average, between a cumulative sum per row and per period. Any time you build time-series metrics — rolling averages, cumulative revenue, trailing windows — you are choosing a frame, whether explicitly or by default. Knowing the bound terms and the ROWS/RANGE distinction lets you produce exactly the metric the business asked for, instead of one that is subtly wrong on tied dates.

Next Steps#

Continue Building Your Skills#

Framing is one of the trickier ideas in SQL, so give yourself room to experiment. Take a small ordered table and try every bound combination — 1 PRECEDING AND 1 FOLLOWING, UNBOUNDED PRECEDING AND CURRENT ROW, CURRENT ROW AND UNBOUNDED FOLLOWING — and predict the output before you run it. Once the frame becomes second nature, running totals and moving averages will feel effortless.