meet it

Windows run after `WHERE`, so filter them in an outer query.

You write SELECT first, but the database has its own : FROM and WHERE, then GROUP BY and HAVING, then window functions, then SELECT, ORDER BY and LIMIT.

So a WHERE cannot test a window's answer, because the window has not been worked out yet. Try it and SQLite stops with misuse of aliased window function.

The fix is a pattern you will use for ever. Number the rows inside a CTE, then filter in the main query. Here it finds each customer's biggest order.

WITH ranked AS (
  SELECT customer_id, id, total,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY total DESC, id
    ) AS n
  FROM orders
)
SELECT customer_id, id, total
FROM ranked
WHERE n = 1
ORDER BY customer_id;

Change n = 1 to n <= 3 and you get the top three in every group.

guess first

The pastry podium, filtered outside. Who is in?

Guess before you run it. Wrong guesses are how you learn, and nothing is marked.

WITH ranked AS (
  SELECT baker, score,
    RANK() OVER (ORDER BY score DESC) AS place
  FROM entries
  WHERE round = 'pastry'
)
SELECT baker, place FROM ranked
WHERE place <= 2
ORDER BY place, baker;

your turn

Your turn. The top two in every round.

Fill the blanks so the numbering restarts for each round and the main query keeps the top two bakers of every round.

Your code

result

Your answer appears here as a table.

fix it

The filter is in the wrong place. Fix it!

Broken code to fix

result

Your answer appears here as a table.

Read the error. Which part of the query is trying to use n?
The WHERE inside the CTE runs before the window, so n does not exist yet.
Move WHERE n = 1 out of the CTE and put it after FROM ranked, then press run.

nailed it

you found the best in every group. :D

Windows run after WHERE, so WHERE cannot test them.
Number rows in a CTE, then filter in the main query.
Use ROW_NUMBER for one winner each, RANK to keep ties.

new words in your collection

evaluation order

This lesson isn’t saved. A free account remembers every lesson you finish.

Windows run after WHERE, so filter them in an outer query.

The pastry podium, filtered outside. Who is in?

Your turn. The top two in every round.

The filter is in the wrong place. Fix it!

you found the best in every group. :D

Windows run after `WHERE`, so filter them in an outer query.