SQL Interview Prep · Lesson

ROWS vs RANGE Framing

The subtle and heavily tested difference between row-based and value-based frames.

ROWS vs RANGE Framing is a free SQL Interview Prep lesson on CoddyKit — lesson 2 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 Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Distinction Interviewers Probe

Once you can write a running total, the natural follow-up is: "What is the difference between ROWS and RANGE in a window frame?" This is a precise mid-level signal. Many candidates use frames daily without ever noticing the two keywords behave differently.

Both define the set of rows the aggregate sees, but they count that set in fundamentally different ways. Get this right and you stand out.

ROWS Counts Physical Rows

ROWS is positional. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means literally the current row plus the two physical rows directly above it in the ordered sequence.

It does not care whether neighboring rows share the same ORDER BY value. Three rows means three rows, period. This is the framing you almost always want for moving averages and strict running totals.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rows_sum
FROM sales;

RANGE Counts by Value

RANGE is value-based. It includes every row whose ORDER BY value falls within a logical range of the current row's value, not a count of rows.

The most important consequence: with RANGE, all rows that tie on the ORDER BY value are treated as a single group of peers. They all get the same frame and therefore the same result.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS range_sum
FROM sales;

The Tie Example That Reveals the Difference

Suppose two sales both happen on 2024-03-02, amounts 30 and 40, preceded by 2024-03-01 with amount 100.

With RANGE to CURRENT ROW: both 03-02 rows are peers, so both show 100 + 30 + 40 = 170.
With ROWS to CURRENT ROW: the first 03-02 row shows 130, the second shows 170, because each row extends the frame one position at a time.

Same data, different numbers. That divergence on ties is the heart of the question.

Why The Default Is RANGE

Recall that OVER (ORDER BY ...) with no explicit frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

This means a naive running total silently uses RANGE. On unique sort keys it is identical to ROWS, so the bug hides. The moment duplicate sort values appear, your running total quietly lumps tied rows together. This is why experienced engineers write ROWS explicitly.

RANGE With Numeric Offsets

RANGE can take a value offset, not just UNBOUNDED. RANGE BETWEEN 7 PRECEDING AND CURRENT ROW over a date or numeric column includes every row whose value is within 7 units of the current value.

Over a date, this gives a true "last 7 days" window that correctly skips missing days, whereas ROWS 7 PRECEDING would grab the previous 7 rows regardless of gaps. Knowing this nuance is a strong answer.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  ) AS last_7_days
FROM sales;

ROWS Ignores Gaps

The flip side: ROWS has no concept of the data's gaps. If your sales table is missing weekends, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW spans 7 recorded days, which may actually cover two calendar weeks.

So the choice hinges on intent: last N records means ROWS; last N units of value (days, dollars) means RANGE with an offset.

Dialect Support Reality Check

An honest interview answer notes support gaps:

PostgreSQL supports RANGE with offsets (since v11) and ROWS fully.
SQL Server supports ROWS and RANGE, but RANGE only with UNBOUNDED/CURRENT ROW, not numeric offsets.
MySQL 8 supports both, with limited RANGE offset types.

If a numeric RANGE offset is unavailable, you emulate it with a self-join or a generated calendar. Mentioning this shows production awareness.

GROUPS: The Third Mode

There is a lesser-known third framing mode: GROUPS. It counts peer groups rather than rows or values. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW includes the current peer group and the one before it.

It is rarely required, but naming it when asked "are there other frame modes?" demonstrates real depth. Support exists in PostgreSQL 11+ and a few others.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS two_day_groups
FROM sales;

A Decision Rule You Can Recite

Boil the whole topic down to one sentence you can say in an interview:

"Use ROWS when I mean a fixed number of physical rows, use RANGE when I mean a logical span of the ordering value, and remember that ties make them diverge because the default RANGE groups equal values together."

That single sentence answers the question completely and confidently.

Side-by-Side Comparison

Putting both framings in one query makes the divergence visible. Run this on data with duplicate dates and compare the two columns row by row.

On unique dates the columns match exactly; on tied dates the RANGE column repeats the same total across peers while the ROWS column climbs one step at a time.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total,
  SUM(amount) OVER (ORDER BY sale_date
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_total
FROM sales;

Quick Check

Two rows tie on the ORDER BY value with a default frame. What happens?

Recap: ROWS vs RANGE

ROWS counts physical rows; RANGE counts by the logical value of the ordering column and groups ties as peers. The default OVER (ORDER BY ...) frame is RANGE, which is why a naive running total can lump duplicate sort values together.

Choose ROWS for "last N records" and gap-insensitive moving windows; choose RANGE with an offset for "last N days/dollars" that respects gaps. Knowing GROUPS exists is a bonus. Next we apply ROWS framing to build moving averages.

Frequently asked questions

Is the “ROWS vs RANGE Framing” lesson free?

Yes — the full text of “ROWS vs RANGE Framing” is free to read here on the web, and the SQL Interview Prep 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 Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “ROWS vs RANGE Framing”?

The subtle and heavily tested difference between row-based and value-based frames. You practise SQL Interview Prep 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 Interview Prep?

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

How long does the “ROWS vs RANGE Framing” 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 Interview Prep lesson?

Yes. Every SQL Interview Prep 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

Cumulative Sums With Window Frames
ROWS vs RANGE Framing
Moving Averages Over a Sliding Window
Cumulative Distribution and Percent of Total

← Back to SQL Interview Prep