Hiring Funnel for Each Open Requisition — SQL Problem
The applicant-tracking system stores, for each application, the furthest stage the candidate reached: applied → screened → interviewed → offered → joined.
- Difficulty: Medium
- Topics: Conditional Logic, Joins
- Dialect: MySQL
- Problem: #234
Problem statement
The applicant-tracking system stores, for each application, the furthest stage the candidate reached: applied → screened → interviewed → offered → joined. A candidate who reached a stage also passed every stage before it.
Return one row for every requisition, including those with no applications, with req_id, role_title, applied (all applications), interviewed (reached interviewed or beyond), offered (reached offered or beyond), joined, and acceptance_pct = joined × 100 / offered rounded to 2 decimals — NULL when nobody was offered. Counts are 0, not NULL, for an empty requisition. Order by req_id.
Tables
Table: Requisition
| Column | Type |
|---|---|
| req_id | int |
| role_title | varchar |
| department | varchar |
| opened_on | date |
Primary key: req_id.
One row per approved opening the recruiters are hiring for.
Table: Application
| Column | Type |
|---|---|
| app_id | int |
| req_id | int |
| candidate_id | int |
| furthest_stage | enum(applied, screened, interviewed, offered, joined) |
Primary key: app_id.
One row per candidate per requisition; furthest_stage is the last stage they reached, whether or not they are still in process.
Examples
Example 1
Requisition
| req_id | role_title | department | opened_on |
|---|---|---|---|
| 71 | Backend Engineer | Engineering | 2025-01-06 |
| 72 | Inside Sales Executive | Sales | 2025-01-13 |
| 73 | Payroll Analyst | Finance | 2025-02-03 |
| 74 | Legal Counsel | Legal | 2025-02-10 |
Application
| app_id | req_id | candidate_id | furthest_stage |
|---|---|---|---|
| 1 | 71 | 9001 | applied |
| 2 | 71 | 9002 | interviewed |
| 3 | 71 | 9003 | offered |
| 4 | 71 | 9004 | joined |
| 5 | 72 | 9005 | screened |
| 6 | 72 | 9006 | offered |
| 7 | 72 | 9007 | offered |
| 8 | 72 | 9008 | joined |
| 9 | 73 | 9009 | interviewed |
Output
| req_id | role_title | applied | interviewed | offered | joined | acceptance_pct |
|---|---|---|---|---|---|---|
| 71 | Backend Engineer | 4 | 3 | 2 | 1 | 50 |
| 72 | Inside Sales Executive | 4 | 3 | 3 | 1 | 33.33 |
| 73 | Payroll Analyst | 1 | 1 | 0 | 0 | NULL |
| 74 | Legal Counsel | 0 | 0 | 0 | 0 | NULL |
How to solve Hiring Funnel for Each Open Requisition
A funnel is cumulative: a candidate whose furthest stage is joined was also interviewed and offered. So each funnel column counts the applications whose stage is at or beyond a point — IN ('offered', 'joined') for offered, and so on — or, cleaner for longer funnels, map each stage to a number (1–5) and compare with >=.
Every requisition is in the answer, so the query starts at Requisition and LEFT JOINs Application. A requisition with no applications produces one row of NULLs: COUNT(a.app_id) gives 0 for it where COUNT(*) would give 1, and each SUM(CASE … ELSE 0 END) gives 0 because the CASE falls to ELSE on NULL.
The acceptance rate divides joined by offered. When nobody was offered that would be a division by zero; NULLIF(offered, 0) turns the divisor into NULL and the whole expression into NULL, which is what the statement asks for (MySQL would give NULL anyway, but other engines raise an error, so say it explicitly). Round to 2 decimals. One pass over the applications grouped by requisition.
Reference solution (MySQL)
SELECT r.req_id, r.role_title,
COUNT(a.app_id) AS applied,
SUM(CASE WHEN a.furthest_stage IN ('interviewed', 'offered', 'joined') THEN 1 ELSE 0 END) AS interviewed,
SUM(CASE WHEN a.furthest_stage IN ('offered', 'joined') THEN 1 ELSE 0 END) AS offered,
SUM(CASE WHEN a.furthest_stage = 'joined' THEN 1 ELSE 0 END) AS joined,
ROUND(SUM(CASE WHEN a.furthest_stage = 'joined' THEN 1 ELSE 0 END) * 100
/ NULLIF(SUM(CASE WHEN a.furthest_stage IN ('offered', 'joined') THEN 1 ELSE 0 END), 0), 2) AS acceptance_pct
FROM Requisition r
LEFT JOIN Application a ON a.req_id = r.req_id
GROUP BY r.req_id, r.role_title
ORDER BY r.req_idAnother way
SELECT req_id, role_title, applied, interviewed, offered, joined,
CASE WHEN offered = 0 THEN NULL ELSE ROUND(joined * 100 / offered, 2) END AS acceptance_pct
FROM (
SELECT r.req_id, r.role_title, COUNT(a.app_id) AS applied,
COUNT(CASE WHEN s.lvl >= 3 THEN 1 END) AS interviewed,
COUNT(CASE WHEN s.lvl >= 4 THEN 1 END) AS offered,
COUNT(CASE WHEN s.lvl = 5 THEN 1 END) AS joined
FROM Requisition r
LEFT JOIN (SELECT app_id, req_id, CASE furthest_stage WHEN 'applied' THEN 1 WHEN 'screened' THEN 2
WHEN 'interviewed' THEN 3 WHEN 'offered' THEN 4 ELSE 5 END AS lvl FROM Application) s ON s.req_id = r.req_id
LEFT JOIN Application a ON a.app_id = s.app_id
GROUP BY r.req_id, r.role_title
) t
ORDER BY req_id← Appraisal Rating Mix by Department · Average Days to Fill a Requisition by Department →