Advanced Window Frames and the QUALIFY Rewrite

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewshard30 minproduct-analyticsROWS vs RANGELAST_VALUE frame trapNTILEnamed WINDOWsubquery filtertop-N-per-group

The LAST_VALUE default-frame trap, ROWS vs RANGE on ties, why a window cannot go in WHERE, NTILE, and the QUALIFY rewrite.

Three window-frame traps interviewers love

Window functions are L4 material, but the frame underneath them is where the senior-flavored questions live. Three things trip people up, and all three are fair game on a screen.

Trap 1: LAST_VALUE returns the current row, not the last row

LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY sale_date) looks like it returns each region's final sale amount. It does not. The default frame, once you supply an ORDER BY, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the window ends at the current row and "last value in the frame" is the value of the current row's last peer on the ORDER BY key. That equals the current row's own value only when the key is unique; on a tie it is the last tied row's value. Either way it is not the partition's final value. To get that, widen the frame:

LAST_VALUE(amount) OVER (
  PARTITION BY region ORDER BY sale_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

FIRST_VALUE does not have this problem, because the default frame already starts at the partition beginning. LAST_VALUE is the one that bites. In the demo below, the two 2026-01-02 rows both report last_value_wrong = 50: they are peers, so they share one frame that runs through the end of their peer group, and both read the last tied row rather than the partition's true final 300.

Trap 2: ROWS and RANGE differ on ties (recap from Level 4)

You proved this in Level 4's running-totals lesson: ROWS counts physical rows, while RANGE folds every row tied on the ORDER BY key into one step, so an unintended RANGE default reads too high on duplicate keys. It resurfaces here because the same tie is what makes LAST_VALUE above misread its peer group, and the demo puts both effects side by side on one tied date.

Trap 3: a window function cannot go in WHERE

WHERE runs before window functions are evaluated, so WHERE ROW_NUMBER() OVER (...) = 1 is an error. Compute the window in a subquery or CTE, then filter the derived column in the outer query. This is the standard top-N-per-group and keep-latest shape:

SELECT region, sale_date, amount FROM (
  SELECT region, sale_date, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date DESC) AS rn
  FROM sales
) WHERE rn = 1;

Interview nuance: NTILE(n) splits a partition into n as-equal-as-possible buckets, which is how you cut customers into spend quartiles or latency deciles. When the earlier buckets come out one row larger than the later ones, that is NTILE distributing the remainder, not a bug.

In the warehouse this differs. Snowflake, BigQuery, and Databricks collapse the subquery-then-filter into one line with QUALIFY: ... QUALIFY ROW_NUMBER() OVER (...) = 1. QUALIFY is to window functions what HAVING is to aggregates. SQLite has no QUALIFY, so the subquery form is what you write here, and it is the portable answer QUALIFY desugars to. The frame semantics (ROWS vs RANGE, the LAST_VALUE default-frame trap) are ANSI standard and identical across every dialect.

Sample data for this example

CREATE TABLE sales (region TEXT, sale_date TEXT, amount INTEGER);
INSERT INTO sales (region, sale_date, amount) VALUES
  ('east', '2026-01-01', 100),
  ('east', '2026-01-02', 200),
  ('east', '2026-01-02', 50),    -- tied date with the row above
  ('east', '2026-01-03', 300);

Worked example (SQL)

-- last_value_wrong follows the current row's last peer (watch the tied date), not the partition final; last_value_right is the true final (300).
-- On the tied date, running_sum_range lumps both peers (350) while running_sum_rows does not.
SELECT sale_date, amount,
  LAST_VALUE(amount) OVER (ORDER BY sale_date) AS last_value_wrong,
  LAST_VALUE(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value_right,
  SUM(amount) OVER (ORDER BY sale_date ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum_rows,
  SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum_range
FROM sales
ORDER BY sale_date, amount DESC;

Apply

Your turn

The task this lesson builds to.

Write a query that returns every sale with the final sale amount in its region, as (region, sale_date, amount, region_final_amount), over sales(region, sale_date, amount).

region_final_amount is the amount of the region's latest sale by date, and it must appear on every row of the region, not only the last one. Use LAST_VALUE with a frame that spans the whole partition (the default frame gives you the current row instead). Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a query that splits customers into 4 spend quartiles with NTILE(4) and returns the min and max spend per quartile, as (quartile, min_spend, max_spend), over customers(customer_id, total_spend).

Order the quartiles 1 through 4, and reuse one named WINDOW for the NTILE call rather than inlining the OVER (...). Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.