Advanced SQL for Ranking | Dataford Interview Questions

Your question is Advanced SQL for Ranking. 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.

Problem

Hinge’s analytics team wants to review the first employees associated with each product project in alphabetical order. Write a PostgreSQL query that returns the top two employees per project based on last name and then first name.

Requirements

Join employees_projects to employees using employee_id.
Rank employees independently within each project_id using ROW_NUMBER().
Sort alphabetically by last_name, then first_name, with employee_id as a deterministic tie-breaker.
Treat null names as appearing after non-null names, remove null employee mappings, and return no more than two employees per project.
Prevent duplicate employee-project mappings from producing duplicate output rows.
Order the final result by project and alphabetical position.

Schema

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
last_name	VARCHAR(80)	Employee surname
first_name	VARCHAR(80)	Employee given name
salary	NUMERIC(12,2)	Annual employee salary
department	VARCHAR(80)	Hinge department

employees_projects

Column	Type	Description
employee_id	INTEGER	Employee associated with the project
project_id	INTEGER	Hinge project identifier