Drill 2 of 12 in Healthcare questions
0

Interview Question Bank · COUNT FILTER · choosing the denominator

2. Doctor utilization

EasyHealthcareQuestion #134

Concept: COUNT FILTER · choosing the denominatorshow

A utilization rate is used capacity over offered capacity. The hard part is the denominator: time that was never offered (holidays, blocked time) isn't idle capacity, so it belongs in neither number. COUNT(*) FILTER (WHERE ...) gives you both counts in one pass, and keeps a group whose numerator is 0 instead of dropping it.

Example (different data):

SELECT room,
  COUNT(*) FILTER (WHERE state = 'occupied') AS used,
  COUNT(*) FILTER (WHERE state <> 'maintenance') AS offered
FROM room_hours
GROUP BY room;

Your task

Larkspur Health's scheduling team reviews how full each doctor's calendar was in the week of 2025-03-03. A slot is 'booked', 'open' (offered but nobody booked it) or 'blocked' (the doctor wasn't available).

For every doctor with slots that week, return doctor (their name), booked_slots, available_slots (booked plus open) and utilization_pct (booked as a % of available, rounded to 1 decimal). Sort by utilization_pct descending, then doctor.

Starting Postgres in your browser…

Checking your access…