Data engineering round
SQL Window Function Partitioning Coding Problem
Written and reviewed by Sahil Srivastav
What this interview round tests
Window functions are where SQL interviews get real. Deduplicating to the latest row per key, ranking within a group, computing a running total — all of them hinge on getting the PARTITION BY, the ORDER BY, and the frame clause exactly right, and each one has a quietly wrong version that returns plausible-looking data.
Interviewers ask this because window logic is unforgiving and common. A ROW_NUMBER() ordered the wrong way keeps the oldest row instead of the newest. A running total with a default frame sums the wrong range. The output has the right shape and the wrong numbers — the hardest kind of bug to catch by eye.
The scenario
You are handed queries running against a small bundled SQLite database (zero setup). One deduplicates to a single row per entity but keeps the wrong one; another computes a running total whose window frame includes rows it should not.
The tests compare your query results against known-correct expected outputs. Your job is to fix the partition keys, the ordering, and the frame so each window function selects and aggregates exactly the rows it should.
What you’ll practice
- PARTITION BY: grouping rows correctly before ranking or numbering
- ORDER BY inside a window: newest-vs-oldest and tie-breaking
- ROW_NUMBER / ranking to deduplicate to one row per key
- Frame clauses (ROWS/RANGE) for correct running aggregates
- Verifying analytic SQL against known-correct expected results
How to approach it
Window bugs live in the three clauses that define a window — how rows are partitioned, how they are ordered within a partition, and what frame the aggregate sees. For each query, ask which of those three does not match the result you actually want; the output shape can be right while one of them is quietly wrong.
Diff your output against the expected results row by row; window bugs rarely fail loudly, so the test fixture is your oracle.
The starter repository ships with a failing test suite and a bundled verify.sh. Reviewed reference solutions are part of Gronex Pro — this page stays spoiler-free on purpose.
FAQ
What database does this use?
A small SQLite database bundled in the repo, so there is nothing to install or configure. The window-function semantics you practise are standard SQL and transfer directly to Postgres, Snowflake, BigQuery, and Spark SQL.
Why are window functions such a common interview topic?
Because they are how real analytical work gets done — deduplication, ranking, running aggregates — and they are easy to get subtly wrong. Getting the partition, order, and frame right is a strong signal that someone has done the job.