# 5 SQL window-function traps that show up in data engineering interviews (with runnable DuckDB examples)

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:

```sql
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

```sql
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:

```sql
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

```sql
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":

```sql
-- 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:

```sql
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.

## 6\. Bonus: a test fixture that catches a missing PARTITION BY

(Thanks to Emily Woods for suggesting this in the comments.) With a single customer, a missing `PARTITION BY customer_id` is invisible: every row is in the same partition anyway. Add a second customer with an order on the same date and the wrong query fails visibly:

```sql
INSERT INTO orders VALUES (5, 2, DATE '2026-01-01', 100);

SELECT order_id, customer_id,
       SUM(amount) OVER (ORDER BY order_date, order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS no_partition,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS per_customer
FROM orders ORDER BY order_id;
```

| order\_id | customer\_id | no\_partition | per\_customer |
| --- | --- | --- | --- |
| 1 | 1 | 10 | 10 |
| 2 | 1 | 15 | 15 |
| 3 | 1 | 135 | 35 |
| 4 | 1 | 142 | 42 |
| 5 | 2 | 115 | 100 |

Customer 2's 100 bleeds into customer 1's running total. That's a nice way to turn these snippets into a small regression set: one fixture per trap, each designed so the *wrong* query visibly fails. (Run this after the other examples; it changes the table.)

* * *

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.*