Drill 5 of 12 in HR questions
0

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…