Filtering rn <= N

Concepts covered: Execution Order, Common Table Expressions, Row Numbering

The most common live error on this question lands 20 seconds after the pattern is recognized. rn is defined in the SELECT list, WHERE rn <= 3 is added to the same query, and the run fails with 'column rn does not exist'. The reason is the order in which the engine evaluates clauses, and an interviewer who sees the error asks for that reason, so explaining it correctly turns the fumble into a point.

SELECT department_id, employee_id, salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;

The order the engine actually runs

• FROM and JOIN: the rows come into existence, carrying the base columns of the table
• WHERE: rows are dropped, with only base columns visible, because nothing has been computed yet
• GROUP BY and HAVING: rows fold into groups and the groups are filtered
• SELECT, including window functions: ROW_NUMBER runs over the surviving rows, and rn is born here
• ORDER BY and LIMIT: the finished rows are arranged and cut

WHERE runs in step 2 and rn appears in step 4, so at the moment WHERE is evaluated the column has no value and no name. This is also why the numbering has to finish before the cutoff can be applied: ROW_NUMBER needs to see every row of a department to know which one is third, and a filter that ran before it would change the rows being numbered. The cutoff has to sit in a query that starts after the numbering query has finished, which means a second SELECT that reads the first.

The shape that works

WITH ranked AS (
  SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked
WHERE rn <= 3;

The CTE named ranked is the whole table with rn attached, 6 rows. The outer query's FROM reads ranked, so by the time its WHERE runs, rn is a base column of the thing being read and the comparison is allowed. The filter keeps rows numbered 3 or lower: department 10 loses 104 at position 4, department 20 keeps both of its rows at positions 1 and 2, and the result has 5 rows. Add 10 people to department 20 and the query returns 3 of them; shrink department 10 to 2 people and it returns those 2. The query never asks how large a group is.

A subquery in the FROM clause does the same job with the same rows: the numbering SELECT goes inside parentheses as FROM (...) AS ranked, and WHERE rn <= 3 follows it. The CTE reads better in the room because the numbering step has a name you can point at, and interviewers follow a named step more readily than a nested one. Snowflake offers QUALIFY rn <= 3, as do BigQuery and DuckDB: a clause that runs after the window functions and saves the second SELECT. It's worth naming as a dialect shortcut, with the CTE as the version that runs everywhere.

Reading the cutoff

rn <= 3 and rn < 4 keep the same rows. rn < 3 keeps 2 per department, the off-by-one that returns the right number of groups and the wrong number of rows. rn = 1 keeps the top row of each department and is the deduplication query. rn > 3 keeps everything past the top 3, the rows a 'who is outside the top 3' question wants. The comparison is the last decision in the query and the one most often typed without being read back.

About this lesson

This section is part of the Top N Per Group: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.