Window Frame

Hook: Interviewers love this because one missing frame can make LAST_VALUE quietly return the wrong answer.

Question: What is a window frame in SQL, and why does it matter?

Answer: A window frame is the specific slice of rows a window function can see for the current row. The full PARTITION BY group is the big bucket; the frame is the smaller moving window inside that bucket. It matters because functions like SUM, AVG, FIRST_VALUE, and LAST_VALUE can change their result depending on where the frame starts and ends.

Interview-Ready Answer: In SQL, a window frame is the row range that a window function evaluates for each current row. I think of the partition as the whole shelf and the frame as the handful of books I am allowed to look at right now. This matters a lot for running totals and especially for functions like LAST_VALUE, because the default frame often ends at the current row unless I explicitly extend it to the end of the partition.

What a Window Frame Really Is

A window function runs on rows that are related to the current row. The PARTITION BY clause splits data into groups, the ORDER BY clause gives those rows an order, and the frame says which rows around the current row are visible to the function.

Think of it like reading a report through a narrow ruler: the report is the partition, the ruler position is the current row, and the visible text under the ruler is the frame.

How It Works Under the Hood

1. The database first builds each partition, if you used PARTITION BY.
2. It sorts each partition by the window ORDER BY keys.
3. For every row, it finds the frame start and frame end based on the frame clause or the default frame.
4. If you use ROWS, the engine counts physical rows. If you use RANGE, it includes rows with equal sort-key values, called peers (rows tied on the ordering expression). If you use GROUPS, it moves by peer groups instead of individual rows.
5. The function reads only rows inside that frame and returns one result for the current row.

Default Behavior You Must Know

The default is easy to forget:

• If you have ORDER BY inside the window and you do not write a frame, many engines follow the SQL-standard default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• If you do not have ORDER BY inside the window, the frame is the entire partition.

This is why a query can look correct and still surprise you. LAST_VALUE is the classic trap: with the default frame, the 'last' row is often just the current row, not the last row in the partition.

ROWS vs RANGE vs GROUPS

When to use which: Use ROWS when you want a precise count of rows, such as 'the last 3 transactions'. Use RANGE when ties should move together, such as 'all sales on the same day'. Use GROUPS when you want to step by tie groups, not by individual rows.

Performance and Complexity

Window functions usually need the data sorted by partition and order keys, so the first big cost is often O(n log n) per partition if the rows are not already ordered. After that, simple running aggregates like cumulative SUM are often computed in near O(n) time by reusing prior state, but wide frames can force the engine to keep more rows in memory. In practice, the memory footprint can grow with partition size, especially when the frame spans many rows and the engine cannot stream it cheaply.

If the input already matches the needed ordering, the optimizer may avoid a full sort or use an index-backed plan, which can make a huge difference on large tables.

Important Edge Cases

• Duplicate order keys: peers matter a lot with RANGE; two rows with the same date can produce the same running total.
• LAST_VALUE trap: without an explicit frame ending at UNBOUNDED FOLLOWING, you often get the current row, not the last row.
• No ORDER BY: the whole partition is the frame, which is fine for partition totals but not for running results.
• Engine differences: some databases support only part of the full frame syntax, and RANGE offset rules can be stricter than ROWS.

Real-World Example: Imagine a checkout service in an e-commerce platform that calculates each customer's running spend for fraud checks and loyalty rewards.

The team groups purchases by customer and orders them by purchase time. For a running total, they use a frame that ends at the current row. But for a report that needs the customer's final spend on every row, they must extend the frame to the end of the partition. If they forget, LAST_VALUE or a final-balance calculation can silently show the wrong amount.

What goes wrong: a developer ships a dashboard using LAST_VALUE(amount) with the default frame. On every row, the dashboard shows the current transaction amount instead of the final amount in the customer partition. Support sees confused merchants, the logs look normal, and the bug hides for days because the query returns valid-looking numbers. The symptom is not an error; it is a believable but wrong report, which is exactly why frame bugs are dangerous.

Follow-up & Tricky Questions:

• What is the difference between ROWS and RANGE?
  ROWS counts individual rows, while RANGE groups rows with equal ordering values. That means peers share the same frame result under RANGE.
• What is the default frame when ORDER BY is present?
  Usually the standard default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is why running aggregates work without an explicit frame, but value functions can surprise you.
• Which window functions ignore the frame?
  Ranking functions like ROW_NUMBER, RANK, and DENSE_RANK do not depend on the frame. They care about partition and ordering, but not the frame boundaries.
• Why does LAST_VALUE often seem broken?
  Because the default frame ends at the current row, so the 'last' row in the frame is often the current row. To get the true last row in the partition, extend the frame to UNBOUNDED FOLLOWING.
• When would you choose GROUPS?
  Use it when tied rows should be treated as one step, such as moving across peer groups in ordered business events. It is less common than ROWS and RANGE, and not every engine supports it equally.
• Tricky: If I write SUM(x) OVER (ORDER BY day), is that always a row-by-row running total?
  No. With duplicates in day, the default is often value-based RANGE, so peers on the same day get the same result. If you need exact row-by-row accumulation, write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Tricky: Does adding a frame change ROW_NUMBER()?
  No. ROW_NUMBER() is driven by ordering within the partition, not by the frame. Frames matter for aggregates and value functions, not for row numbering.
• Tricky: Is the window ORDER BY the same as the final query ORDER BY?
  No. The window ORDER BY controls how the function sees rows; the final ORDER BY controls output display. They can be different, and that is a common source of confusion.

Common Mistakes:

• Forgetting the default frame: candidates assume LAST_VALUE means the whole partition, but the default often stops at the current row. Correction: explicitly write UNBOUNDED FOLLOWING when you need the true last row.
• Using RANGE when they wanted exact rows: ties collapse together and change the result. Correction: use ROWS for precise row counts.
• Confusing partition and frame: the partition is the full group; the frame is the current slice. Correction: remember 'partition = bucket, frame = window inside the bucket'.
• Assuming frames affect every window function: ranking functions ignore frames. Correction: know which functions are frame-sensitive and which are not.

Memory Hook: Partition is the library; frame is the open book pages under your finger.

Cheat Sheet:

• PARTITION BY = which group.
• ORDER BY = row order inside the group.
• Frame = which rows the function can see right now.
• ROWS = physical rows.
• RANGE = peer values move together.
• LAST_VALUE needs a careful frame.

Practice Tasks:

• Write a running total with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Change the query to RANGE and observe what happens when two rows share the same date.
• Fix a broken LAST_VALUE query so it returns the true final row in the partition.