0Pricing
SQL Academy · Lesson

Frame Clauses: ROWS vs RANGE

Choose between ROWS BETWEEN and RANGE BETWEEN frame clauses, and understand how each handles duplicates and gaps.

Frame Clauses: ROWS vs RANGE is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Frame Inside OVER

A window function evaluates over a frame — a subset of rows from the partition. The frame is defined by:

OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN <start> AND <end>     -- or RANGE BETWEEN ...
)

Default Frame

If you specify ORDER BY but no frame, the default is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- "From the first row up to all rows tied with the current row".

ROWS Counts Physically

ROWS counts N rows in the partition order, regardless of value:

SUM(x) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- The current row plus the previous 2.  Exactly 3 rows.

RANGE Counts by Value

RANGE counts rows whose ORDER BY value falls within a window of the current value:

SUM(x) OVER (
  ORDER BY day
  RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
)
-- All rows within 2 days (by calendar) of the current row.

Why the Distinction Matters

If there are gaps in the data, ROWS and RANGE give different answers:

-- Days: 1, 3, 5 (no 2 or 4)
-- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for day 5: rows 1,3,5 = 3 days
-- RANGE BETWEEN INTERVAL '2 days' PRECEDING for day 5: only days 3,5 = 2 days

GROUPS Frame (PG 11+)

GROUPS counts peer groups (ties on ORDER BY) rather than individual rows:

SUM(x) OVER (
  ORDER BY tier
  GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
)
-- "The current tier and the previous tier" — useful when ORDER BY has many ties.

Frame Bounds

  • UNBOUNDED PRECEDING — first row of the partition
  • n PRECEDING
  • CURRENT ROW
  • n FOLLOWING
  • UNBOUNDED FOLLOWING — last row

Running Total

Sum from the start to here:

SUM(x) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- Same as the default frame.

Trailing Window

The N-day trailing average:

AVG(revenue) OVER (
  ORDER BY day
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS avg_7d

Centred Window

Look both ways:

AVG(revenue) OVER (
  ORDER BY day
  ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
) AS centred_7d

LAST_VALUE Surprise

LAST_VALUE with default frame returns the current row, not the last in the partition. Use explicit unbounded frame:

LAST_VALUE(name) OVER (
  PARTITION BY group ORDER BY day
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

EXCLUDE Clause (PG 11+)

Skip certain rows in the frame:

SUM(x) OVER (
  ORDER BY day
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  EXCLUDE CURRENT ROW
)
-- All previous 6 days but not today.

Recap

Frame clauses give precise control over windows.

  • ROWS — physical row count
  • RANGE — value-based interval
  • GROUPS — peer groups
  • Default frame may surprise you — be explicit

Quick Check

You have daily revenue with missing days. Which frame gives you "all rows within 7 calendar days before now"?

Frequently asked questions

Is the “Frame Clauses: ROWS vs RANGE” lesson free?

Yes — the full text of “Frame Clauses: ROWS vs RANGE” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Frame Clauses: ROWS vs RANGE”?

Choose between ROWS BETWEEN and RANGE BETWEEN frame clauses, and understand how each handles duplicates and gaps. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Frame Clauses: ROWS vs RANGE” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Frame Clauses: ROWS vs RANGE
  2. Lag/Lead with Frame Windows
  3. Bucketing with NTILE and Cume_Dist
  4. Real-World Reporting Patterns
← Back to SQL Academy