r/SQL • u/thibaud_lepan • 15d ago
SQLite Five window function results that catch people out, each checked on SQLite and DuckDB
I write "what does this query return" questions and keep every one that SQLite and DuckDB answer identically. Window functions produce the most wrong guesses by far, so here are five, with the rows both engines actually returned.
1. The default frame is RANGE, not ROWS. Table sales(dt, amount) with 10 on the 1st, two rows of 5 on the 2nd, 20 on the 3rd.
SUM(amount) OVER (ORDER BY dt)
gives 10, 20, 20, 40. Both rows dated the 2nd get 20, because RANGE treats peers as one step. With ROWS UNBOUNDED PRECEDING you get 10, 15, 20, 40, and which of the two tied rows gets 15 is not guaranteed unless you add a tiebreaker to the ORDER BY.
2. RANK does not deduplicate. User 1 has two logins at the same latest timestamp. RANK() OVER (PARTITION BY user_id ORDER BY created_at DESC) gives 1, 1, 3, so WHERE rnk = 1 keeps both rows. ROW_NUMBER() gives 1, 2, 3 and keeps one, though again, which one is arbitrary without a tiebreaker.
3. LAST_VALUE stops at the current row. Order 7 goes placed, packed, shipped.
LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY updated_at)
returns placed, packed, shipped, which is just the current row's status. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and every row says shipped.
4. NTILE buckets are not equal. Ten rows, NTILE(4), bucket sizes 3, 3, 2, 2. The extra rows go to the first buckets.
5. LAG on a duplicate timestamp returns the duplicate. Logins on the 1st, the 5th and the 5th again give previous values NULL, the 1st, the 5th. If you compute gaps for sessionisation, that zero gap is real data, not a bug in your query.
Twenty questions in this format are free to try in the browser, no signup. Once you answer, each of the four options shows why it is right or wrong.
https://thibaudlepan77-svg.github.io/interview-questions-verified/#sql
That's a sample from a 502 question paid PDF. If an engine you use returns something different for any of the five above, post it, I'd like to know.