A window function cannot be filtered without a derived table

Window functions are computed after WHERE and GROUP BY, so a predicate on the result has nowhere to go in the same query block.

-- error: window function in WHERE
SELECT sku, ROW_NUMBER() OVER w AS rn FROM sales
WHERE rn <= 3
WINDOW w AS (PARTITION BY category ORDER BY sales DESC);

-- the wrapper, which is the only way in 8.0
SELECT * FROM (
  SELECT sku, category,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) rn
  FROM sales
  WHERE placed_at >= '2019-01-01'      -- restrict HERE, where an index works
) x WHERE rn <= 3;

Putting the restriction inside the derived table rather than outside is the part people leave out, because both versions return the same rows — but the inner predicate can use an index and the outer one runs after every row has been ranked. QUALIFY exists in some other databases and does not in MySQL, so the wrapper is not a workaround for a missing feature so much as the idiom. Nothing about the wrapper prevents the optimiser merging it, which is why the plan usually looks better than the SQL.