# Where SQL window functions quietly go wrong in interviews

Source: https://www.techinterview.org/post/3233476114/sql-window-functions-interview-gotchas/
Updated: 2026-07-02 · techinterview.org

Ask a candidate for the second-highest salary in a table and you learn more in two minutes than most take-homes tell you. The ones who reach for `LIMIT 1 OFFSET 1` usually haven't thought about ties. The ones who write a correlated subquery are correct but slow to explain. And the ones who reach for a window function and then set the frame wrong are the interesting case, because they know the tool and still walk into the one sharp edge that separates people who have shipped analytics queries from people who read about them last week.

Window functions show up in almost every data-heavy interview loop now, from analytics engineering roles to backend positions at fintech shops that grade you on SQL fluency. The concept is simple: compute a value across a set of rows related to the current row, without collapsing those rows the way `GROUP BY` does. The trouble is in the corners.

## The three ranking functions and the ties nobody plans for

Every ranking question is really a question about what happens on a tie. Give someone this data and ask them to rank by salary descending, and the difference between the functions falls out immediately.

| salary | ROW_NUMBER() | RANK() | DENSE_RANK() |
| --- | --- | --- | --- |
| 5000 | 1 | 1 | 1 |
| 5000 | 2 | 1 | 1 |
| 4200 | 3 | 3 | 2 |
| 4000 | 4 | 4 | 3 |

`ROW_NUMBER` gives every row a unique integer, so the two people earning 5000 get 1 and 2 in some order. `RANK` ties them at 1 and then skips to 3. `DENSE_RANK` ties them at 1 and keeps counting without a gap. If the interviewer follows up with "which of the two 5000-earners is row 1?" the correct answer is that `ROW_NUMBER` picks arbitrarily unless your `ORDER BY` breaks the tie completely. That non-determinism is a real bug in production if you page results or dedupe on it, and it's exactly the kind of thing a good interviewer probes.

The nth-highest-salary question is where the choice actually matters. Reaching for `OFFSET` is fragile because two people can share a salary, and then "the second-highest salary" and "the second row" are different things. `DENSE_RANK` over distinct salary values is the answer that survives ties.


```
SELECT DISTINCT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;
```


Use `RANK` here instead and you can still filter on the value, but the moment three people tie for the top salary, `rnk = 2` returns nothing at all, because `RANK` jumped straight from 1 to 4. That silent empty result is a favorite trap.

## Why you can't put a window function in the WHERE clause

This is the single most common mistake I see, and it comes from not knowing the logical order SQL evaluates a query in. The engine resolves `FROM` and joins first, then `WHERE`, then `GROUP BY` and `HAVING`, and only then the `SELECT` list where your window functions live. Your `ORDER BY` and `LIMIT` come after that.

So this fails:


```
-- error: rnk does not exist yet when WHERE runs
SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
WHERE rnk = 2;
```


The window function hasn't been computed at the point `WHERE` is applied, so the column simply isn't there. You have to compute it in a subquery or CTE and filter in an outer query, which is why the nth-salary answer above is nested. Candidates who understand this explain it in one sentence. Candidates who don't tend to sprinkle the query with parentheses until the error goes away.

If the interview is on Snowflake, BigQuery, or DuckDB, there's a cleaner path worth knowing: the `QUALIFY` clause filters on window results directly, the way `HAVING` filters on aggregates.


```
SELECT name, salary
FROM employees
QUALIFY DENSE_RANK() OVER (ORDER BY salary DESC) = 2;
```


Postgres and MySQL don't have `QUALIFY`, so mentioning it and then noting it's engine-specific is a small signal that you've actually worked across warehouses rather than one local database.

## Top-N per group, and picking the right function for it

"Give me the top earner in each department" is the workhorse window-function question, and it's really the ranking table plus a `PARTITION BY`. The partition restarts the numbering for each group.


```
SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department
           ORDER BY salary DESC
         ) AS rn
  FROM employees
) t
WHERE rn = 1;
```


Now the follow-up: what if two people tie for the top salary in a department? `ROW_NUMBER` returns exactly one of them, chosen arbitrarily. `DENSE_RANK` or `RANK` with `rnk = 1` returns both. Neither is wrong, but they answer different business questions, and saying out loud which one you want ("do you need exactly one row per department, or every person tied for the lead?") is the difference between a query and a considered query. For top-3, the same swap matters more, because ties near the cutoff change how many rows come back.

## The running-total trap: ROWS versus RANGE

Here's the gotcha that catches people who think they've mastered this. Write a running total and you probably write something like this:


```
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
```


Looks fine. It usually is fine. But the default frame when you supply an `ORDER BY` and no explicit frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, and `RANGE` treats all rows sharing the current row's `ORDER BY` value as peers. If two orders fall on the same date, both rows get the same running total, the end-of-day cumulative figure, not a strict row-by-row accumulation. Most of the time that's what you want for a daily total. It is not what you want if you expected each row to add exactly one increment.

To get strict row-by-row behavior, name the frame explicitly with `ROWS`:


```
SELECT order_date, amount,
       SUM(amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders;
```


The `ROWS` frame counts physical rows and ignores ties, so two same-date orders now produce two different cumulative values. An interviewer who hands you data with duplicate keys and asks for a running total is testing exactly this. Knowing that `RANGE` is the default, and that it behaves differently on ties, is one of those details that reads as experience rather than memorization.

## PARTITION BY is not GROUP BY

`GROUP BY` collapses each group into a single output row. A window function keeps every input row and adds a computed column alongside it. That means you can show each employee's salary next to their department average in the same result set, which a plain aggregate can't do without a self-join.


```
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
```


The confusion shows up when a question needs both. "Rank each product by total sales within its category" is a two-step problem: aggregate the raw sales into per-product totals with `GROUP BY` first, then rank those totals with a window function in an outer query. Trying to do both in one pass is where people tangle themselves, because you can't rank a sum you haven't computed yet. Aggregate, then rank. Say that structure before you write it and the query almost writes itself.

## LAG and LEAD, and the gap that quietly breaks them

Period-over-period math is the other big family of questions. Month-over-month revenue growth is the standard version, and `LAG` pulls the prior row's value onto the current row.


```
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue;
```


Two things bite people. First, the earliest row has no predecessor, so `LAG` returns `NULL` and your change column is `NULL` for row one. That's usually correct, but if you need a zero there, `LAG(revenue, 1, 0)` supplies a default. Second, and this is the subtle one, `LAG` steps back one *row*, not one *month*. If March is missing from the data, the April row compares itself to February and calls it month-over-month, silently. Nothing errors. The number is just wrong. When the data can have gaps, you either fill the calendar first with a date spine or join on an explicit prior-period key instead of trusting row order.

If a candidate reaches for `LAG` confidently and then, unprompted, asks whether the monthly data is guaranteed to be dense, that's the tell I care about. The syntax is learnable in an afternoon. Knowing that ordered offsets lie when rows are missing is the part that comes from having been burned by a real dashboard.

The thing interviewers actually watch for across all of these is not whether you can recite that `DENSE_RANK` exists. It's whether you ask which database before you write, because the frame defaults, the `QUALIFY` support, and even the tie behavior of `ORDER BY` differ between Postgres, MySQL, and the cloud warehouses. The strong candidates ask first, then write once.