Skip to content
Tutorials Center
My learning

Topics

Lesson 53 of 125 ยท Window Functions

Window Frames in Depth

Master SQL window frames. Compare ROWS, RANGE and GROUPS, set frame boundaries, use EXCLUDE, share named windows with WINDOW and filter aggregates with FILTER.

Every aggregate window function works on a frame: the subset of the partition that counts for the current row. So far you have used frames almost by accident: the default one that creates running totals, and ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for moving averages. This lesson takes the frame apart so you can build any window you need.

You'll learn the three frame units (ROWS, RANGE and GROUPS) and how they differ when values tie, all the boundary options, value-based windows such as "the last 7 days", the EXCLUDE clause, named windows with WINDOW, and the FILTER clause. SQLite supports all of these, which makes it a great place to learn them; the dialect table at the end shows where other databases differ.

The full syntax

A window definition has up to three parts. The frame is the last one:

SQL
OVER (
  PARTITION BY ...
  ORDER BY ...
  { ROWS | RANGE | GROUPS } BETWEEN frame_start AND frame_end
  [ EXCLUDE { NO OTHERS | CURRENT ROW | GROUP | TIES } ]
)

The start and end can each be one of:

Boundary Meaning
UNBOUNDED PRECEDING The first row of the partition
n PRECEDING n units before the current row
CURRENT ROW The current row (for RANGE/GROUPS: its first or last peer)
n FOLLOWING n units after the current row
UNBOUNDED FOLLOWING The last row of the partition

The start must not come after the end: BETWEEN CURRENT ROW AND 1 PRECEDING is an error. If you write only a start, as in ROWS 3 PRECEDING, the end is CURRENT ROW.

Which functions care about frames? Aggregates (SUM, AVG, COUNT, MIN, MAX, group_concat...) and FIRST_VALUE, LAST_VALUE, NTH_VALUE. Ranking functions and LAG/LEAD ignore the frame entirely.

The defaults

You rarely see a frame written out because the defaults cover the common cases:

  • No ORDER BY in the window: the frame is the whole partition. SUM(x) OVER (PARTITION BY g) is the group total.
  • With ORDER BY: the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, from the start of the partition to the current row and its peers (rows with an equal ORDER BY value).

That word "peers" is what the three units disagree about.

ROWS vs RANGE vs GROUPS

The unit decides what "1 PRECEDING" or "CURRENT ROW" means:

  • ROWS counts physical rows. One row back is exactly one row back.
  • RANGE works with values of the single ORDER BY column. CURRENT ROW includes all peers, and 5 PRECEDING means "rows whose value is at most 5 less than mine".
  • GROUPS counts groups of peers. One group back means the previous distinct value, however many rows have it.

A small table with deliberate ties makes the difference visible. Points are scored on days 1, 2, 2, 3, 5 and 5:

SQL ROWS, RANGE and GROUPS side by side

Press Run (or Ctrl + Enter) to see the result here.

Walk through the row for day 3 (40 points):

  • rows_1 = 70: the previous physical row (day 2, 30 points) plus itself.
  • range_1 = 90: all rows with day between 2 and 3 (20 + 30 + 40).
  • groups_1 = 90: the previous peer group (both day-2 rows) plus its own group.

Now day 5 (50 points), where the difference between RANGE and GROUPS shows:

  • range_1 = 110: days between 4 and 5. There is no day 4, so only the two day-5 rows count.
  • groups_1 = 150: the previous group is day 3, even though it is two days away, so 40 + 50 + 60.

Notice also that tied rows (the two day-2 rows, the two day-5 rows) always get the same range_1 and groups_1 values, but different rows_1 values. With ROWS, the database picks an order within the ties, so make the ORDER BY unique whenever you use ROWS.

Unit Counts Ties Good for
ROWS Physical rows Split, order is arbitrary "Last N rows", moving averages over complete series
RANGE Distance in value Always together Time windows ("last 7 days"), value bands
GROUPS Distinct values Always together "Last N distinct dates" when some dates are missing

Value-based windows: the last 7 days

RANGE with a number is how you write a true time window. Row-based windows silently stretch over gaps; a value-based window doesn't. It needs exactly one numeric ORDER BY expression, so convert dates to day numbers with julianday():

SQL Orders in the last 7 days ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

May 2025 has orders on only 13 different days. The RANGE column counts orders whose day is within the 7 days ending on the current day (6 days back plus today). The ROWS column counts the last 7 rows, which can span two weeks or more when days are missing, so the two disagree once the gaps start: on May 18, the last seven rows reach back to May 2 (9 orders), but only the 4 orders placed since May 12 are within 7 days.

Other databases let you write the interval directly. PostgreSQL 11+ and MySQL 8 accept RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW (MySQL spells it INTERVAL 6 DAY) over a date column. SQL Server doesn't support RANGE with an offset at all; use a self join or ROWS over a gap-free calendar instead.

Looking ahead, centering and whole partitions

Frames can extend past the current row with FOLLOWING. Here are three frames in one query:

SQL Frames that look ahead ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

  • centered_avg_3 averages each Finance employee with the one hired before and the one hired after (only two rows at the edges).
  • payroll_hired_since is a reverse running total: the salaries of this person and everyone hired after them.
  • everyone uses the whole partition even though the window has an ORDER BY. This is the frame LAST_VALUE needs, as you saw in LAG, LEAD and Comparing Rows.

EXCLUDE: leaving rows out of the frame

The EXCLUDE clause removes rows from an otherwise normal frame:

  • EXCLUDE NO OTHERS (the default): exclude nothing.
  • EXCLUDE CURRENT ROW: drop the current row itself.
  • EXCLUDE GROUP: drop the current row and all its peers.
  • EXCLUDE TIES: drop the peers but keep the current row.

The classic use is comparing a value with the others in its group, without the value pulling the average toward itself:

SQL Average of colleagues, excluding yourself ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Nadia, the head of sales, earns 126,000. Including her, the Sales average is 86,500; her colleagues alone average 73,333. Excluding yourself is the fair comparison in performance reviews, A/B tests and "how do I compare with my peers?" features.

Named windows: the WINDOW clause

When several functions share a window, repeating it is noisy and error-prone. The WINDOW clause defines it once, after HAVING and before ORDER BY. A named window can also be extended: OVER (w ROWS ...) reuses w's partition and order and adds a frame.

SQL Shared and extended named windows ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

by_salary builds on whole_dept by adding an ORDER BY. The rules: a window that extends another can add an ORDER BY only if the base has none, and it can't override the base's PARTITION BY. A window with a frame can't be extended. ROWS UNBOUNDED PRECEDING is shorthand for "from the start to the current row".

FILTER: conditional window aggregates

The FILTER (WHERE ...) clause tells an aggregate to look only at matching rows, while the window still covers all rows. It works on plain aggregates and on window aggregates, and it's often clearer than SUM(CASE WHEN ... END):

SQL Running counts by status with FILTER ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first two orders of 2025 were both cancelled, so the running cancellation rate starts at 100% and falls to 20% by the end of January. Note the order of keywords: function(...) FILTER (WHERE ...) OVER (...).

Frame pitfalls checklist

  • Ties with ROWS: make the ORDER BY unique, or results can change between runs.
  • RANGE with offsets needs exactly one ORDER BY expression, and it must be numeric (in SQLite).
  • Missing periods break row counts; use RANGE on a number of days, or generate a calendar with a recursive CTE.
  • LAST_VALUE with the default frame returns the current row; extend the frame to UNBOUNDED FOLLOWING.
  • Frames don't affect ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG or LEAD.

Dialect support

Feature SQLite PostgreSQL MySQL 8 SQL Server
ROWS frames Yes Yes Yes Yes (2012+)
RANGE with UNBOUNDED/CURRENT ROW Yes Yes Yes Yes
RANGE with numeric offset Yes (3.28+) Yes (11+) Yes, plus INTERVAL No
GROUPS Yes (3.28+) Yes (11+) No No
EXCLUDE Yes (3.28+) Yes (11+) No No
WINDOW clause Yes Yes Yes 2022+
FILTER (WHERE ...) Yes (3.30+) Yes No No

Where FILTER is missing, write the condition inside the aggregate: SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) OVER w.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the default frame of SUM(x) OVER (ORDER BY d)?

    With ORDER BY, the default is RANGE up to the current row, so tied rows (peers) are included together.
  2. 2. Days 1, 2, 2, 4. For the day-4 row, what does COUNT(*) OVER (ORDER BY day GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) return?

    GROUPS counts peer groups. The previous group is day 2 (two rows), plus the current group (one row), so 3.
  3. 3. Same data. What does RANGE BETWEEN 1 PRECEDING AND CURRENT ROW count for the day-4 row?

    RANGE uses values. Only rows with day between 3 and 4 count, and only the day-4 row qualifies.
  4. 4. Which clause compares an employee's salary with the average of their colleagues only?

    EXCLUDE CURRENT ROW removes the row itself from the frame, so the average covers everyone else in the partition.
  5. 5. Where does FILTER go in a window aggregate?

    FILTER directly follows the aggregate call, before OVER.

Summary

  • A frame selects which rows of the partition an aggregate (or FIRST_VALUE/LAST_VALUE/NTH_VALUE) uses for the current row.
  • ROWS counts rows, RANGE measures value distance and keeps ties together, GROUPS counts distinct values.
  • Without ORDER BY the frame is the whole partition; with it, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  • RANGE with a numeric offset over julianday() gives true time windows such as "the last 7 days".
  • EXCLUDE CURRENT ROW / GROUP / TIES remove rows from the frame.
  • WINDOW name AS (...) shares and extends window definitions; FILTER (WHERE ...) makes aggregates conditional.

Next: Percentiles, Medians and Statistics, where distribution functions, medians and gaps-and-islands complete the window toolkit.