OPD New-Patient Retention by Monthly Cohort — SQL Problem
A clinic chain measures whether new patients come back. A patient's cohort is the month of their first visit.
- Difficulty: Hard
- Topics: Dates, Subqueries, Aggregation
- Dialect: MySQL
- Problem: #219
Problem statement
A clinic chain measures whether new patients come back. A patient's cohort is the month of their first visit. The patient is retained when they have at least one visit in the calendar month right after the cohort month (a visit later in the cohort month itself does not count).
For every cohort, return cohort_month ('YYYY-MM'), new_patients, retained and retention_pct = 100 × retained ÷ new_patients, rounded to 2 decimals. Order by cohort_month.
Tables
Table: Visit
| Column | Type |
|---|---|
| visit_id | int |
| patient_id | int |
| visit_date | date |
Primary key: visit_id.
One row per OPD consultation. A patient may visit several times in a month.
Examples
Example 1
Visit
| visit_id | patient_id | visit_date |
|---|---|---|
| 1 | 1 | 2024-11-05 |
| 2 | 1 | 2024-12-02 |
| 3 | 2 | 2024-11-20 |
| 4 | 2 | 2024-11-28 |
| 5 | 3 | 2024-11-30 |
| 6 | 3 | 2025-01-10 |
| 7 | 4 | 2024-12-15 |
| 8 | 4 | 2025-01-03 |
| 9 | 4 | 2025-01-20 |
| 10 | 5 | 2024-12-31 |
Output
| cohort_month | new_patients | retained | retention_pct |
|---|---|---|---|
| 2024-11 | 3 | 1 | 33.33 |
| 2024-12 | 2 | 1 | 50 |
How to solve OPD New-Patient Retention by Monthly Cohort
Retention analysis works in two layers. The first collapses the visits to one row per patient — MIN(visit_date) is the first visit, and its month is the cohort. The second decides, per patient, whether a visit exists in the following calendar month, and then aggregates those yes/no flags per cohort.
Calendar months are compared most safely as a month index, YEAR × 12 + MONTH: December 2024 is followed by January 2025 because the index simply increases by one, with no special case for the year boundary, and visits elsewhere in the cohort month or two months later do not match. An EXISTS subquery turns the check into a 0/1 flag, so COUNT(*) is the cohort size, SUM(flag) the retained patients, and their ratio × 100, rounded to 2 decimals, the rate.
The alternative computes the next month's label with DATE_ADD on the first day of the cohort month (never on the visit date itself — adding a month to 31 January would clamp to February's last day), joins to the visits to find returners, and LEFT JOINs them back so cohorts with no returner still show 0.
Reference solution (MySQL)
WITH firsts AS (
SELECT patient_id, MIN(visit_date) AS first_visit
FROM Visit
GROUP BY patient_id
), flagged AS (
SELECT f.patient_id, DATE_FORMAT(f.first_visit, '%Y-%m') AS cohort_month,
CASE WHEN EXISTS (
SELECT 1 FROM Visit v
WHERE v.patient_id = f.patient_id
AND YEAR(v.visit_date) * 12 + MONTH(v.visit_date) = YEAR(f.first_visit) * 12 + MONTH(f.first_visit) + 1
) THEN 1 ELSE 0 END AS came_back
FROM firsts f
)
SELECT cohort_month, COUNT(*) AS new_patients, SUM(came_back) AS retained,
ROUND(100 * SUM(came_back) / COUNT(*), 2) AS retention_pct
FROM flagged
GROUP BY cohort_month
ORDER BY cohort_monthAnother way
WITH firsts AS (
SELECT patient_id, DATE_FORMAT(MIN(visit_date), '%Y-%m') AS cohort_month,
DATE_FORMAT(DATE_ADD(CONCAT(DATE_FORMAT(MIN(visit_date), '%Y-%m'), '-01'), INTERVAL 1 MONTH), '%Y-%m') AS next_month
FROM Visit GROUP BY patient_id
), returners AS (
SELECT DISTINCT f.patient_id FROM firsts f JOIN Visit v ON v.patient_id = f.patient_id AND DATE_FORMAT(v.visit_date, '%Y-%m') = f.next_month
)
SELECT f.cohort_month, COUNT(*) AS new_patients, COUNT(r.patient_id) AS retained,
ROUND(COUNT(r.patient_id) * 100 / COUNT(*), 2) AS retention_pct
FROM firsts f LEFT JOIN returners r ON r.patient_id = f.patient_id
GROUP BY f.cohort_month ORDER BY f.cohort_month← Patients With Three or More No-Shows in a Row · Insurance Claim Funnel by Insurer →