Average Days to Fill a Requisition by Department
Talent acquisition measures time to fill: the days from the date a requisition was opened to the date of its first accepted offer.
- Difficulty: Medium
- Topics: Dates, Joins, Aggregation
- Dialect: MySQL
- Problem: #235
Problem statement
Talent acquisition measures time to fill: the days from the date a requisition was opened to the date of its first accepted offer. Offers that were never accepted (accepted_on NULL) do not fill anything; a requisition without an accepted offer is not filled and is left out.
Return one row per department with at least one filled requisition: department, filled (the number of filled requisitions), avg_days (the average time to fill, rounded to 1 decimal) and max_days. Order by avg_days descending, then department ascending.
Tables
Table: Requisition
| Column | Type |
|---|---|
| req_id | int |
| department | varchar |
| opened_on | date |
Primary key: req_id.
Table: Offer
| Column | Type |
|---|---|
| offer_id | int |
| req_id | int |
| candidate_id | int |
| offered_on | date |
| accepted_on | date |
Primary key: offer_id.
A requisition can have several offers (a declined one, then another). accepted_on is NULL for an offer that was declined or is still pending.
Examples
Example 1
Requisition
| req_id | department | opened_on |
|---|---|---|
| 81 | Engineering | 2025-01-06 |
| 82 | Engineering | 2025-01-20 |
| 83 | Sales | 2025-02-03 |
| 84 | Sales | 2025-02-10 |
| 85 | Finance | 2025-02-17 |
Offer
| offer_id | req_id | candidate_id | offered_on | accepted_on |
|---|---|---|---|---|
| 1 | 81 | 7001 | 2025-02-10 | NULL |
| 2 | 81 | 7002 | 2025-02-24 | 2025-02-27 |
| 3 | 82 | 7003 | 2025-02-14 | 2025-02-19 |
| 4 | 83 | 7004 | 2025-02-20 | 2025-02-21 |
| 5 | 83 | 7005 | 2025-03-03 | 2025-03-04 |
| 6 | 84 | 7006 | 2025-03-10 | NULL |
| 7 | 85 | 7007 | 2025-03-05 | 2025-03-14 |
Output
| department | filled | avg_days | max_days |
|---|---|---|---|
| Engineering | 2 | 41 | 52 |
| Finance | 1 | 25 | 25 |
| Sales | 1 | 18 | 18 |
How to solve Average Days to Fill a Requisition by Department
Two levels of aggregation. First, per requisition, the fill date is MIN(accepted_on) over its offers — NULLs (declined or pending offers) are ignored by MIN, and a requisition whose offers are all unaccepted has no row once we filter accepted_on IS NOT NULL. Doing this before the department step matters: joining every offer straight to the requisition and averaging would count a requisition once per offer.
Then join the per-requisition fill dates to Requisition, take DATEDIFF(first_accept, opened_on) and aggregate by department: COUNT(*) filled requisitions, AVG rounded to 1 decimal, and MAX. The inner join is what drops unfilled requisitions, and departments with none filled disappear with them.
The alternative computes the fill date with a correlated scalar subquery per requisition (NULL when none accepted) and filters the NULLs before grouping. Both are one pass over offers plus one over requisitions with an index on Offer.req_id.
Reference solution (MySQL)
SELECT r.department, COUNT(*) AS filled,
ROUND(AVG(DATEDIFF(f.first_accept, r.opened_on)), 1) AS avg_days,
MAX(DATEDIFF(f.first_accept, r.opened_on)) AS max_days
FROM Requisition r
JOIN (SELECT req_id, MIN(accepted_on) AS first_accept FROM Offer WHERE accepted_on IS NOT NULL GROUP BY req_id) f
ON f.req_id = r.req_id
GROUP BY r.department
ORDER BY avg_days DESC, r.departmentAnother way
WITH filled AS (
SELECT r.req_id, r.department,
DATEDIFF((SELECT MIN(o.accepted_on) FROM Offer o WHERE o.req_id = r.req_id), r.opened_on) AS days
FROM Requisition r
)
SELECT department, COUNT(days) AS filled, ROUND(AVG(days), 1) AS avg_days, MAX(days) AS max_days
FROM filled
WHERE days IS NOT NULL
GROUP BY department
ORDER BY avg_days DESC, department← Hiring Funnel for Each Open Requisition · High Performers Due for a Promotion Review →