SQL Mid-level (2–5 yrs) Coding Very frequent SQL window functions RANK PARTITION BY CTE

Write a query using window functions to rank employees by salary within each department.

sql-mid-002

Your answer

Answer as you would in a real interview — explain your thinking, not just the conclusion.

Model answer

Window functions operate over a partition of rows without collapsing them into groups the way GROUP BY does. RANK() assigns the same rank to tied rows and leaves a gap after the tie, DENSE_RANK() leaves no gap, and ROW_NUMBER() always assigns a unique sequential number. I use PARTITION BY department_id to reset the ranking per department and ORDER BY salary DESC to rank highest earners first. Because window functions run after WHERE and GROUP BY but before HAVING and ORDER BY, I wrap them in a CTE or subquery when I need to filter on the computed rank.

Code example

SELECT
    id,
    name,
    department_id,
    salary,
    RANK()         OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
    DENSE_RANK()   OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk,
    ROW_NUMBER()   OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    AVG(salary)    OVER (PARTITION BY department_id)                      AS dept_avg_salary
FROM employees;

-- Top 2 per department (keeps ties with RANK)
WITH ranked AS (
    SELECT *,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT * FROM ranked WHERE rnk <= 2;

Follow-up

How would you return only the top-2 earners per department? What happens if two employees are tied for second place with RANK() versus ROW_NUMBER()?