0Pricing
SQL Interview Prep · Lesson

Filtering on a Window Result

Why you must wrap a window function in a subquery or CTE to filter on it.

Filtering on a Window Result is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why You Cannot Filter a Window in WHERE

A frequent interview "gotcha": writing WHERE ROW_NUMBER() OVER (...) = 1 throws an error. Window functions are not allowed in WHERE, GROUP BY, or HAVING.

The reason is logical execution order. WHERE runs to select rows before window functions are evaluated. The window has not even been computed yet, so it cannot be referenced in a filter.

The Execution-Order Explanation

Window functions are computed in a dedicated phase that sits after FROM, WHERE, GROUP BY, and HAVING, but before the final ORDER BY and LIMIT.

So at the moment WHERE runs, the rank or row number does not exist. To filter on it, you must let the window finish first, then filter the produced column in an outer query layer.

The Subquery Wrapper Pattern

The standard fix: compute the window function in an inner query (a derived table), alias the result, then filter that alias in the outer WHERE.

The derived table must have an alias (t here) — interviewers note candidates who forget it. Now rn is an ordinary column the outer query can compare.

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

The CTE Pattern (Often Cleaner)

A Common Table Expression does the same job with more readable structure. Define the ranking in a WITH step, then filter it in the main query.

Functionally identical to the subquery, but interviewers usually prefer CTEs in live coding because the intent reads top to bottom.

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

Worked Example: Top-N Per Group

The highest-frequency window problem: "top 3 highest-paid employees per department." Rank inside the CTE, then keep rn <= 3 outside.

Choose the ranking function by tie behavior: ROW_NUMBER caps at exactly 3 rows per department; switch to RANK/DENSE_RANK if ties at the boundary must be included.

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

Worked Example: Filtering on a Running Total

The wrapper pattern is not just for ranks. Any window result — running totals, moving averages, LAG deltas — must be filtered the same way.

Here we compute a running balance, then keep only the rows where it first exceeded 1000. The filter lives outside the window layer.

WITH balances AS (
  SELECT
    account_id, txn_date, amount,
    SUM(amount) OVER (
      PARTITION BY account_id ORDER BY txn_date
    ) AS running_balance
  FROM transactions
)
SELECT *
FROM balances
WHERE running_balance > 1000;

QUALIFY: The Shortcut in Some Databases

Snowflake, BigQuery, Teradata, and DuckDB offer a QUALIFY clause that filters window results directly — no wrapper needed. It runs after window functions, exactly where you want.

Mention QUALIFY to show breadth, but note it is not standard SQL and is absent from PostgreSQL, MySQL, and SQL Server, where you still need the subquery/CTE.

-- Snowflake / BigQuery only:
SELECT department, name, salary
FROM employees
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY department ORDER BY salary DESC
) = 1;

Do Not Confuse HAVING With Window Filtering

Candidates sometimes try HAVING to filter a rank. HAVING filters groups after GROUP BY aggregation and still runs before window functions, so it cannot reference a window column either.

  • WHERE → filters rows before grouping and before windows.
  • HAVING → filters aggregated groups, still before windows.
  • Filtering a window → needs an outer query (or QUALIFY).

Combining a Pre-Filter With a Window Filter

Often you filter both before and after the window. Apply ordinary row filters in the inner WHERE (so the window only sees relevant rows), then filter the window result in the outer query.

In this example we first restrict to active employees, then pick each department's top earner among them. Putting the WHERE active inside changes which rows are ranked.

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
  WHERE is_active = true        -- pre-filter before ranking
)
SELECT * FROM ranked
WHERE rn = 1;                   -- post-filter on the window

Performance Note

Interviewers may ask if the wrapper hurts performance. Usually not: the optimizer treats the subquery/CTE as part of one plan and computes the window once. There is no extra scan just because you wrapped it.

One caveat: in some engines a CTE can be an optimization fence (materialized), so for hot paths a derived table or QUALIFY may plan better. Profile with EXPLAIN if it matters.

Common Mistakes

Final checklist:

  • Never put a window function in WHERE/HAVING — it errors.
  • Always alias the derived table; an unnamed subquery in FROM is rejected.
  • Pick the ranking function for the tie behavior the question needs.
  • Use QUALIFY only where supported; otherwise fall back to the CTE/subquery wrapper.

Quick Check

Why does filtering a window function require a wrapper?

Recap: Filtering Window Results

You closed the loop on ranking window functions:

  • Window functions run after WHERE/GROUP BY/HAVING, so you cannot filter them there.
  • Wrap the window in a subquery or CTE (always aliased) and filter the result in the outer query.
  • This drives top-N-per-group, latest-row-per-key, and running-total thresholds.
  • QUALIFY is a handy non-standard shortcut in Snowflake/BigQuery only.

You now have the full ranking toolkit interviewers test most.

Frequently asked questions

Is the “Filtering on a Window Result” lesson free?

Yes — the full text of “Filtering on a Window Result” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Filtering on a Window Result”?

Why you must wrap a window function in a subquery or CTE to filter on it. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Filtering on a Window Result” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. OVER, PARTITION BY and ORDER BY
  2. ROW_NUMBER for Unique Sequencing
  3. RANK vs DENSE_RANK on Ties
  4. Filtering on a Window Result
← Back to SQL Interview Prep