Average Days to Fill a Requisition by Department

Medium
235.

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. 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

ColumnType
req_idint
departmentvarchar
opened_ondate

Primary key: req_id.

Table: Offer

ColumnType
offer_idint
req_idint
candidate_idint
offered_ondate
accepted_ondate

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.

Example

The rows must come back in the order shown.

Requisition

req_iddepartmentopened_on
81Engineering2025-01-06
82Engineering2025-01-20
83Sales2025-02-03
84Sales2025-02-10
85Finance2025-02-17

Offer

offer_idreq_idcandidate_idoffered_onaccepted_on
18170012025-02-10NULL
28170022025-02-242025-02-27
38270032025-02-142025-02-19
48370042025-02-202025-02-21
58370052025-03-032025-03-04
68470062025-03-10NULL
78570072025-03-052025-03-14

Output

departmentfilledavg_daysmax_days
Engineering24152
Finance12525
Sales11818
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.

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

ColumnType
req_idint
departmentvarchar
opened_ondate

Primary key: req_id.

Table: Offer

ColumnType
offer_idint
req_idint
candidate_idint
offered_ondate
accepted_ondate

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_iddepartmentopened_on
81Engineering2025-01-06
82Engineering2025-01-20
83Sales2025-02-03
84Sales2025-02-10
85Finance2025-02-17

Offer

offer_idreq_idcandidate_idoffered_onaccepted_on
18170012025-02-10NULL
28170022025-02-242025-02-27
38270032025-02-142025-02-19
48370042025-02-202025-02-21
58370052025-03-032025-03-04
68470062025-03-10NULL
78570072025-03-052025-03-14

Output

departmentfilledavg_daysmax_days
Engineering24152
Finance12525
Sales11818

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.department

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