Top Clinics by Patient Volume | Dataford Interview Questions

Your question is Top Clinics by Patient Volume. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Oracle Health EHR operations leaders need a ranked view of clinic utilization. Write a PostgreSQL query to identify the five clinics with the highest patient volume during calendar year 2025.

Requirements

Join clinics to visits and consider only visits with visit_status = 'completed' from 2025.
Calculate patient volume as the number of distinct non-null patients per clinic, so repeat visits by one patient count once.
Use a window function to assign a deterministic rank, returning exactly the top five clinics. Break volume ties by the smaller clinic_id.
Return clinic_name, city, patient_volume, and clinic_rank, ordered by rank.

Schema

clinics

Column	Type	Description
clinic_idPK	INTEGER	Unique clinic identifier
clinic_name	VARCHAR(100)	Oracle Health EHR clinic name
city	VARCHAR(80)	Clinic city

visits

Column	Type	Description
visit_idPK	INTEGER	Unique visit identifier
clinic_id	INTEGER	Associated clinic identifier
patient_id	INTEGER	Patient identifier
visit_date	DATE	Date of the visit
visit_status	VARCHAR(20)	Visit completion status