Top Contractors by Department | Dataford Interview Questions

Your question is Top Contractors by Department. Start with the requirements and the two tables on the right.

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

Business Context

General Dynamics Information Technology needs a department-level view of contractor compensation for workforce planning and staffing reviews.

Write a PostgreSQL query that returns the three highest-paid contractors within each department.

Join contractors to their department records.
Exclude contractors whose annual salary is NULL.
Use a ranking window function partitioned by department.
Return the contractor name, department name, annual salary, and rank. Include no more than three contractors per department, with ties resolved deterministically by contractor name.
Order the final output by department name and rank.

departments

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

contractors

Column	Type	Description
contractor_idPK	INT	Unique contractor identifier
department_id	INT	Assigned department identifier
contractor_name	VARCHAR(100)	Contractor's name
annual_salary	NUMERIC(12,2)	Annual contractor salary