Active Patient Cohort SQL | Dataford Interview Questions

Write a SQL query to find the active patient cohort that visited a primary care physician more than three times in the last calendar year.

Assume the last calendar year is 2025. Treat patients with active_status = TRUE as active, and count only visits to physicians whose specialty is Primary Care.

Output

One row per qualifying patient.
Columns: patient_id, patient_name, and visit_count.
Include only patients with more than three qualifying visits in 2025.
Order by visit_count descending, then patient_id ascending.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient's display name
active_status	BOOLEAN	Whether the patient is currently active

visits

Column	Type	Description
visit_idPK	INT	Unique visit identifier
patient_id	INT	Patient associated with the visit
physician_id	INT	Physician associated with the visit
visit_date	DATE	Date of the visit

physicians

Column	Type	Description
physician_idPK	INT	Unique physician identifier
physician_name	VARCHAR(100)	Physician's display name
specialty	VARCHAR(100)	Physician specialty

Your question is Active Patient Cohort SQL. 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