Interview Question Bank · Date subtraction · PERCENTILE_CONT · status filter
5. Median time to fill by department
MediumHRQuestion #185
Concept: Date subtraction · PERCENTILE_CONT · status filtershow
Subtracting two dates gives whole days, so closed_on - opened_on is each requisition's time to fill. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) is the median, and it works per group with GROUP BY. Decide which rows the metric describes before aggregating: a cancelled requisition also has a closing date, but nobody was hired.
Example (different data):
SELECT clinic, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY seen_on - booked_on) AS median_wait_days FROM appointments WHERE status = 'attended' GROUP BY clinic;
Your task
Quillmoor's recruiting team is setting hiring timelines for next year and wants the typical time it took to fill a role in each department.
For filled requisitions only, return department, filled_reqs and median_days (the PERCENTILE_CONT median of closed_on - opened_on, rounded to 1 decimal). Sort by median_days descending, then department.
Starting Postgres in your browser…
Checking your access…