Top Patients by Visits per Clinic | Dataford Interview Questions

Write a SQL query to find the top-ranked patients by number of visits within each clinic.

Use the clinics, patients, and visits tables. Count every visit record, including visits with a NULL visit date. Patients without visits and clinics without qualifying patients should not appear.

Output

One row per top-ranked patient within a clinic.
Columns: clinic_id, clinic_name, patient_id, patient_name, visit_count, and patient_rank.
Include all patients tied for the highest visit count in their clinic. Order by clinic_id, then patient_id.

Schema

clinics

Column	Type	Description
clinic_idPK	INT	Unique clinic identifier
clinic_name	VARCHAR(100)	Clinic name

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
clinic_id	INT	Clinic associated with the patient
patient_name	VARCHAR(100)	Patient display name
date_of_birth	DATE	Patient date of birth

visits

Column	Type	Description
visit_idPK	INT	Unique visit identifier
patient_id	INT	Patient attending the visit
visit_date	DATE	Date of the visit

Your question is Top Patients by Visits per Clinic. Start with the requirements and the three tables in the Question tab.

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

Problem