Window functions are the most common SQL topic in data engineering interviews, and most candidates know the syntax. The follow-up questions are where people stumble. Here are five traps, each with a tiny example you can paste into DuckDB (DuckDB CLI: curl https://install.duckdb.org | sh or brew install duckdb, then run duckdb; or Python: pip install duckdb, then duckdb.sql("<query>").show()).
Setup:
CREATE TABLE orders AS SELECT * FROM (VALUES
(1, 1, DATE '2026-01-01', 10),
(2, 1, DATE '2026-01-01', 5),
(3, 1, DATE '2026-01-02', 20),
(4, 1, DATE '2026-01-12', 7)
) t(order_id, customer_id, order_date, amount);
1. The default frame is RANGE, not ROWS
SELECT order_id, order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS default_frame,
SUM(amount) OVER (ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM orders ORDER BY order_id;
| order_id | order_date | amount | default_frame | rows_frame |
|---|---|---|---|---|
| 1 | 2026-01-01 | 10 | 15 | 10 |
| 2 | 2026-01-01 | 5 | 15 | 15 |
| 3 | 2026-01-02 | 20 | 35 | 35 |
| 4 | 2026-01-12 | 7 | 42 | 42 |
With an ORDER BY and no frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: rows with the same order_date are peers and are all included. Always write the frame.
2. ROW_NUMBER without a tie-breaker is non-deterministic
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) must number orders 1 and 2 (same date) in some order, and nothing says which one gets number 1. Reruns can return different rows. Add a unique tie-breaker:
SELECT * FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY order_date, order_id) = 1;
If ties are meaningful ("top 3 including ties"), use RANK or DENSE_RANK instead.
3. LAST_VALUE returns the current row
SELECT order_id,
LAST_VALUE(amount) OVER (ORDER BY order_date, order_id) AS looks_wrong,
LAST_VALUE(amount) OVER (ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS actual_last
FROM orders ORDER BY order_id;
looks_wrong is just each row's own amount (10, 5, 20, 7), because the default frame ends at the current row. actual_last is 7 everywhere.
4. WHERE runs before window functions
"Show the running total, but only display January 2nd onwards":
-- wrong: rows before Jan 2 are removed BEFORE the sum is computed
SELECT order_id,
SUM(amount) OVER (ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders WHERE order_date >= DATE '2026-01-02'; -- 20, 27
-- right: compute first, filter after
SELECT * FROM (
SELECT order_id, order_date,
SUM(amount) OVER (ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders
) WHERE order_date >= DATE '2026-01-02'; -- 35, 42
Logical order: FROM → WHERE → GROUP BY → HAVING → window → QUALIFY → SELECT → ORDER BY.
5. "Last 7 rows" is not "last 7 days"
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts rows. On sparse data (no orders between Jan 2 and Jan 12) that window reaches back weeks. Use a time-based frame where your engine supports it:
SELECT order_id, order_date,
SUM(amount) OVER (ORDER BY order_date
RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW) AS last_7_days
FROM orders ORDER BY order_id; -- 15, 15, 35, 7
or build a date spine (one row per day, COALESCE missing days to 0) and then use ROWS. BigQuery, for example, only allows numeric RANGE offsets, so you would order by UNIX_DATE(order_date) there.
The likely follow-up questions in an interview: "How does this scale in Spark?" (a window with ORDER BY sorts each partition; one huge customer causes skew) and "How would you do the 'latest record per key' incrementally?" (deduplicate the batch, then MERGE).
These come from the Data Engineering Interview Kit 2026, which has 80 questions with trade-offs, common mistakes and follow-ups, plus a SQL practice set whose solutions are all executed in DuckDB. Free sample (20 questions): https://payhip.com/b/18A5n
About me: I work as an independent data engineer and DevOps freelancer (IT-Craftsman) and have delivered several data engineering and DevOps projects for large companies. I also work for a technology consulting firm, where I conduct hiring interviews for data engineers and DevOps engineers.
All SQL in this post was executed in DuckDB.
Top comments (1)
Useful sixth test would be adding a second customer with an order on the same date. The single-customer fixture won't expose a missing PARTITION BY customer_id, so a query can pass every current example and still mix customers' running totals. Nice little way to turn these examples into a regression set.