Top Contractors by Department | Dataford Interview Questions

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

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the two tables are in the Question tab.

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

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the two tables are in the Question tab.