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…