Hiring Funnel for Each Open Requisition

Medium
234.

Hiring Funnel for Each Open Requisition

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

ColumnType
req_idint
role_titlevarchar
departmentvarchar
opened_ondate

Primary key: req_id.

One row per approved opening the recruiters are hiring for.

Table: Application

ColumnTypeValues
app_idint
req_idint
candidate_idint
furthest_stageenumapplied, 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.

Example

The rows must come back in the order shown.

Requisition

req_idrole_titledepartmentopened_on
71Backend EngineerEngineering2025-01-06
72Inside Sales ExecutiveSales2025-01-13
73Payroll AnalystFinance2025-02-03
74Legal CounselLegal2025-02-10

Application

app_idreq_idcandidate_idfurthest_stage
1719001applied
2719002interviewed
3719003offered
4719004joined
5729005screened
6729006offered
7729007offered
8729008joined
9739009interviewed

Output

req_idrole_titleappliedinterviewedofferedjoinedacceptance_pct
71Backend Engineer432150
72Inside Sales Executive433133.33
73Payroll Analyst1100NULL
74Legal Counsel0000NULL
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
req_idint
role_titlevarchar
departmentvarchar
opened_ondate

Primary key: req_id.

One row per approved opening the recruiters are hiring for.

Table: Application

ColumnType
app_idint
req_idint
candidate_idint
furthest_stageenum(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_idrole_titledepartmentopened_on
71Backend EngineerEngineering2025-01-06
72Inside Sales ExecutiveSales2025-01-13
73Payroll AnalystFinance2025-02-03
74Legal CounselLegal2025-02-10

Application

app_idreq_idcandidate_idfurthest_stage
1719001applied
2719002interviewed
3719003offered
4719004joined
5729005screened
6729006offered
7729007offered
8729008joined
9739009interviewed

Output

req_idrole_titleappliedinterviewedofferedjoinedacceptance_pct
71Backend Engineer432150
72Inside Sales Executive433133.33
73Payroll Analyst1100NULL
74Legal Counsel0000NULL

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_id

Another 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 →