Window functions: ranking

The idea

Window functions calculate across related rows without collapsing them into groups. Read the query line by line and identify the input table, the transformation, and the shape of the result before running it.

SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM employees ORDER BY department, salary_rank;

Explain it without looking

In your own words, explain the main SQL feature in today’s lesson and why it answers the question.

Recall prompt: Use ROW_NUMBER with PARTITION BY department. Close this page section mentally and explain the query from memory before proceeding.

Run your own query

Rank employees by salary within each department, highest salary first.

Use ROW_NUMBER with PARTITION BY department.

Close the loop

• Run the query and inspect its output.
• Change one part of the query and predict what will happen.
• Return later and rewrite the query from memory.