Top Earners by Department with Window Functions

Brillio's People Analytics team needs a department-level view of its highest-paid active employees. Write a PostgreSQL query that ranks employees within each department using a window function and returns the top five.

Requirements

Join employees to departments using department_id.
Include only employees whose employment_status is Active and whose salary is not NULL.
Use a window function partitioned by department, ranking salary from highest to lowest. Use employee_id as a deterministic tie-breaker.
Return no more than five employees per department, ordered by department and rank.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	References departments.department_id
salary	NUMERIC(12,2)	Annual salary
employment_status	VARCHAR(20)	Current employment status

Your question is Top Earners by Department with Window Functions. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem