✏️ Explanatory Question

Window frames deep-dive — ROWS vs RANGE and the default-frame bug

👁 8 Views
📘 Detailed Answer
🟢 Easy
← Previous LEAD and LAG — compare each row to the previous/next (month-over-month growth)
💡

Answer with Explanation

101

Window frames deep-dive — ROWS vs RANGE and the default-frame bug

Level: Hard — A deep gotcha most candidates miss; the default frame silently produces wrong running totals with duplicate ORDER BY values.

Scenario: You write a running total with SUM(amount) OVER (ORDER BY sale_date) and it looks fine — until two sales share the same date. Then the running total jumps, showing the same larger value for both rows instead of incrementing one at a time. Nothing looks wrong in the query. What's happening?

Sample Data — sales (note the duplicate date)

idsale_dateamount
12026-01-01100
22026-01-02200
32026-01-0250
42026-01-03300

The cause: When you specify ORDER BY but no explicit frame, MySQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, "current row" includes all peer rows that share the same ORDER BY value. So both Jan-02 rows are treated as one group — each shows the total of both.

Predict the Output — ROWS vs RANGE

iddateamountRANGE (default) ❌ROWS ✓
101-01100100100
201-02200350300
301-0250350350
401-03300650650

With RANGE, both Jan-02 rows show 350 (the "peers" are lumped together). With ROWS, they correctly increment: 300 then 350.

ROWS vs RANGE — The Core Difference

  • ROWS: Counts physical rows — "the current row and N rows before it," regardless of their values. Precise, row-by-row.
  • RANGE: Counts by value peers — all rows with the same ORDER BY value are treated as a single logical position (they get the same result).
The fix & the rule: Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for running totals. Never rely on the default frame — it silently switches to RANGE and produces "peer-grouped" results whenever ORDER BY values tie.

The Bug and the Fix

-- THE BUG: no frame -> defaults to RANGE -> ties get lumped
SELECT id, sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;
-- Both Jan-02 rows show 350

-- THE FIX: explicit ROWS frame -> true row-by-row running total
SELECT id, sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;
-- Correct: 100, 300, 350, 650

-- EVEN BETTER: add a tie-breaker so order is deterministic
SELECT id, sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales;

Common Frame Patterns

FrameMeaning
ROWS UNBOUNDED PRECEDINGRunning total (start → current)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW7-row moving window
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING3-row centered window
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGReverse running total
Interviewer follow-up: "When is RANGE actually the correct choice?" → When you genuinely want peers treated together — e.g., a "cumulative total by date" where all sales on the same date should share one cumulative figure. RANGE isn't a bug; it's just the wrong default for row-by-row running totals. Know which semantics you need.
← Previous LEAD and LAG — compare each row to the previous/next (month-over-month growth)