OPD New-Patient Retention by Monthly Cohort

Hard
219.

OPD New-Patient Retention by Monthly Cohort

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

ColumnType
visit_idint
patient_idint
visit_datedate

Primary key: visit_id.

One row per OPD consultation. A patient may visit several times in a month.

Example

The rows must come back in the order shown.

Visit

visit_idpatient_idvisit_date
112024-11-05
212024-12-02
322024-11-20
422024-11-28
532024-11-30
632025-01-10
742024-12-15
842025-01-03
942025-01-20
1052024-12-31

Output

cohort_monthnew_patientsretainedretention_pct
2024-113133.33
2024-122150
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
visit_idint
patient_idint
visit_datedate

Primary key: visit_id.

One row per OPD consultation. A patient may visit several times in a month.

Examples

Example 1

Visit

visit_idpatient_idvisit_date
112024-11-05
212024-12-02
322024-11-20
422024-11-28
532024-11-30
632025-01-10
742024-12-15
842025-01-03
942025-01-20
1052024-12-31

Output

cohort_monthnew_patientsretainedretention_pct
2024-113133.33
2024-122150

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_month

Another 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 →