Advanced · Lesson 6 of 18

CTEs with Window Functions

CTEs + Window Functions = powerful combo!

Find top earner per department:

WITH ranked AS (
    SELECT first_name, department_id, salary,
           RANK() OVER (PARTITION BY department_id
                       ORDER BY salary DESC) as rnk
    FROM employees
)
SELECT * FROM ranked WHERE rnk = 1;

The CTE adds rankings, outer query filters to rank 1.

This "Top N per group" pattern is used constantly!

Check your understanding

Use a CTE with RANK to find the highest-paid employee in each department.

Examples run on the employees sample database. Open any of them in the playground to experiment.