SQL for Recruitment Conversion by Department | Dataford Interview Questions

Your question is SQL for Recruitment Conversion 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.

Problem

UiPath People Operations wants to monitor how effectively each department converts applicants into hires. Write a PostgreSQL query that reports recruitment pipeline conversion by department for applications received during the first quarter of 2025.

Requirements

Return every department, including departments with no qualifying applications.
Show total applications, hired applications, and the conversion rate as a percentage, calculated as hired applications divided by total applications.
Treat missing or non-hired statuses as not hired, avoid division-by-zero errors, and sort by conversion rate descending, with departments without applications last.

Schema

departments

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

applications

Column	Type	Description
application_idPK	INT	Unique application identifier
department_id	INT	Department receiving the application
applied_at	DATE	Date the application entered the recruitment pipeline
status	VARCHAR(30)	Current application pipeline status