Code Quiz

Filtering Window Function Results

Spot why filtering directly on a window function fails and how a CTE fixes it.

Codesql

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

This query is meant to return the top-paid employee per department, but it fails. What is the bug?