Top 10 Patients by Visits | Dataford Interview Questions

Massachusetts General Hospital needs a patient-volume report for operational planning. Write a PostgreSQL query that identifies the 10 patients with the most qualifying visits recorded during calendar year 2025.

Requirements

Count visits for each patient from 2025-01-01 through 2025-12-31.
Exclude visits whose status is Cancelled or is NULL.
Include patients with no qualifying visits, using a count of zero when necessary.
Return the top 10 patients, ordered by visit count descending and patient_id ascending as a deterministic tie-breaker.

Schema

patients

Column	Type	Description
patient_idPK	INTEGER	Unique patient identifier
first_name	VARCHAR(50)	Patient first name
last_name	VARCHAR(50)	Patient last name
date_of_birth	DATE	Patient date of birth

visits

Column	Type	Description
visit_idPK	INTEGER	Unique visit identifier
patient_id	INTEGER	Patient associated with the visit
visit_date	DATE	Date of the visit
status	VARCHAR(20)	Visit status
encounter_type	VARCHAR(30)	Type of clinical encounter

Your question is Top 10 Patients by Visits. Start with the requirements and the two 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