SQL / WINDOW FUNCTIONS
Filtering on window results with a wrapping query
Filter on a window function's result by computing it in a subquery or CTE and applying WHERE in the wrapping query, and know when to filter inside instead.
What you will learn
- Wrap a window query in a subquery or CTE and filter its alias in the outer WHERE
- Explain why WHERE and HAVING run before window functions are computed
- Choose between filtering inside the subquery (changes the window) or outside (does not)
- Reach for QUALIFY only on engines that have it (Snowflake, BigQuery, DuckDB)
Understanding Filtering on window results with a wrapping query
A window function is evaluated as part of the SELECT list, and the SELECT list is one of the last things a query does. By the time SUM(...) OVER (...) or ROW_NUMBER() runs, FROM, WHERE, GROUP BY and HAVING have already fixed the set of rows that the window will look at. That ordering is exactly why WHERE cannot mention a window result: the value does not exist yet, and it could not exist consistently, because the rows WHERE would remove are part of what the window is summing. Engines refuse the query rather than guess at a resolution order.
The fix is to give the query two levels. The inner query computes the window value and names it; the outer query then sees a plain rowset in which that name is an ordinary column, no different from amount or region, so WHERE, GROUP BY, or even a second window function can use it. A derived table, FROM ( ... ) AS t, and a WITH clause are interchangeable here; the CTE form just reads better once you nest more than once or want to reuse the intermediate result.
Where you put a row filter decides what the window sees. A predicate in the inner query shrinks the population before the window is computed, so totals, averages and ranks are worked out over the survivors only; the same predicate in the outer query lets the window see everything and then discards rows, leaving the computed numbers untouched. Neither placement is more correct, they answer different questions, and no error will tell you which one you wrote. Say the sentence out loud, average over the whole region versus average over the large sales only, and the placement follows from the wording.
CREATE TABLE sales (rep TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
('Ada', 'north', 900),
('Brin', 'north', 400),
('Cyd', 'south', 300),
('Dara', 'south', 250),
('Emi', 'west', 1200);
-- Keep every detail row, but only from regions that sold more than 1000.
SELECT rep, region, amount, region_total
FROM (
SELECT rep,
region,
amount,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
) AS per_region
WHERE region_total > 1000
ORDER BY region, amount DESC;A window function is computed after WHERE has already chosen the rows, so filtering on its result needs a second query level where that result is just another column.
Worked examples
WHERE runs too early
Shows what happens when the window expression is written directly in WHERE, on the same sales table.
SELECT rep, region, amount
FROM sales
WHERE SUM(amount) OVER (PARTITION BY region) > 1000;Example explained
Line 1The expression itself is valid; the complaint is about position, since WHERE is evaluated before any window is computed.
Line 2Permitting it would be circular: the rows WHERE removes are the same rows that SUM() OVER would have to add up.
Line 3Wording differs by engine: SQLite reports 'misuse of window function sum()', MySQL 8 reports 'You cannot use the window function sum in this context'.
Line 4Moving the identical expression into a subquery's SELECT list and comparing against its alias outside makes it legal without changing the arithmetic.
Inside versus outside the subquery
The same predicate placed in the inner and then the outer query returns the same rows but different window values.
-- A: filter inside, so the window only ever sees rows with amount >= 300
SELECT rep, region, amount, region_total
FROM (
SELECT rep, region, amount,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
WHERE amount >= 300
) AS t
ORDER BY region, rep;
-- B: filter outside, so the window sees every row first
SELECT rep, region, amount, region_total
FROM (
SELECT rep, region, amount,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
) AS t
WHERE amount >= 300
ORDER BY region, rep;Example explained
Line 1Both versions return the same four rows, so a quick glance at the row count hides the difference entirely.
Line 2In A the inner WHERE discards Dara (250) before the window runs, so south totals only 300.
Line 3In B the window totals 300 + 250 = 550 for south, and Dara's row is dropped afterwards by the outer WHERE.
Line 4North and west are identical in both because no row there was below 300, which is why this bug survives casual testing.
One row per partition with a CTE
The top-per-group pattern: number the rows in a CTE, then filter on the number in the wrapping query.
WITH ranked AS (
SELECT rep, region, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales
)
SELECT rep, region, amount
FROM ranked
WHERE rn = 1
ORDER BY region;Example explained
Line 1The CTE is just a named wrapping query, so rn arrives in the outer query as an ordinary integer column that WHERE can test.
Line 2Changing the condition to rn <= 2 gives the top two per region without touching the OVER clause.
Line 3The numbers are fixed inside ranked, so no outer filter can renumber them; rn = 1 always means the region's largest amount.
Line 4Snowflake, BigQuery, DuckDB and Teradata allow QUALIFY ROW_NUMBER() OVER (...) = 1 in a single level; PostgreSQL, MySQL and SQLite do not.
Filtering on a window value derived from two windows
Compares each row against its partition total in the outer query, keeping rows worth more than a third of their region.
SELECT rep, region, amount, region_total
FROM (
SELECT rep, region, amount,
SUM(amount) OVER (PARTITION BY region) AS region_total,
COUNT(*) OVER (PARTITION BY region) AS reps
FROM sales
) AS t
WHERE reps > 1 AND amount * 3 > region_total
ORDER BY region, rep;Example explained
Line 1Two window columns are computed once in the inner query and both are available to the outer WHERE.
Line 2reps > 1 removes west, where Emi is the only rep, showing that group-level conditions no longer need GROUP BY.
Line 3amount * 3 > region_total is written as multiplication rather than division to avoid integer division truncating the comparison.
Line 4Brin fails because 400 * 3 = 1200 is not greater than 1300, while both south reps pass against their smaller total.
Important notes
PostgreSQL and MySQL reject a derived table with no alias (subquery in FROM must have an alias); that error is about nesting syntax, not about the window function.
A condition on a window column usually cannot be pushed down past the window, so the inner query still computes the window over every row it selects; keeping genuine population filters inside helps both meaning and cost.
Common mistakes
Trying HAVING region_total > 1000 instead of nesting: HAVING is also evaluated before the SELECT list so it fails identically, and adding the GROUP BY that HAVING expects collapses the per-row detail the window function was there to preserve.
Dropping a row filter into the inner query out of habit: the window recomputes over the smaller set, so a partition total, average or running sum silently changes value and no error is raised.
Expecting ranks to renumber after the outer filter: WHERE rn >= 3 returns rows still numbered 3, 4, 5, and renumbering requires a second wrapping level with a fresh ROW_NUMBER over the already-filtered rows.
Try it yourself
Change, predict, then run
Add ('Fen','north',100) to sales, then write a wrapping query returning rep, amount and amount * 1.0 / region_total AS share, keeping only rows where share > 0.5. Now add WHERE amount >= 300 to the inner query, run it again, and note which shares moved even though the same reps came back.
Open the SQL workspaceCheck your understanding
A subquery computes AVG(amount) OVER (PARTITION BY region) AS region_avg. You write WHERE amount > 500 once in the inner query and once in the outer query. What is the difference between the two versions?
- The inner version computes region_avg from only the rows above 500; the outer version computes it from all rows and then discards the rest
- Nothing differs, because the optimizer pushes the predicate down and both plans end up identical
- The outer version is invalid, since WHERE cannot be applied to a derived table that contains a window function
- The inner version returns more rows, because the window is recomputed after the filter and re-admits rows
Show answer
A window function is computed over whatever rows survive to the SELECT list, so filtering earlier changes AVG's input and therefore its value; here the row set is the same in both versions and only region_avg differs. The optimizer answer is tempting because engines do move predicates around freely, but a predicate that would change a window's input cannot be pushed across that window, precisely because the two queries mean different things.