Diabetic Patients Whose Latest HbA1c Improved — SQL Problem
The diabetes clinic tracks each patient's HbA1c (%) over time; lower is better.
- Difficulty: Medium
- Topics: Window Functions, Subqueries
- Dialect: MySQL
- Problem: #213
Problem statement
The diabetes clinic tracks each patient's HbA1c (%) over time; lower is better. A patient has improved when their latest reading is strictly lower than the reading just before it.
Return patient_id, previous_hba1c and latest_hba1c for every improved patient, ordered by patient_id. Patients with a single reading cannot be judged and are left out; an unchanged reading is not an improvement.
Tables
Table: HbA1cReading
| Column | Type |
|---|---|
| patient_id | int |
| test_date | date |
| hba1c | decimal |
Primary key: patient_id, test_date.
One row per patient per test day; hba1c is a percentage with one decimal.
Examples
Example 1
HbA1cReading
| patient_id | test_date | hba1c |
|---|---|---|
| 1 | 2024-09-10 | 8.4 |
| 1 | 2024-12-12 | 7.9 |
| 1 | 2025-03-15 | 7.1 |
| 2 | 2024-10-01 | 7 |
| 2 | 2025-01-05 | 7.6 |
| 3 | 2025-02-20 | 6.8 |
| 4 | 2024-11-11 | 9.2 |
| 4 | 2025-02-14 | 9.2 |
| 5 | 2024-08-08 | 6.4 |
| 5 | 2025-01-09 | 8.1 |
| 5 | 2025-04-02 | 7.5 |
Output
| patient_id | previous_hba1c | latest_hba1c |
|---|---|---|
| 1 | 7.9 | 7.1 |
| 5 | 8.1 | 7.5 |
How to solve Diabetic Patients Whose Latest HbA1c Improved
Two facts are needed per patient: the latest reading and the one before it. Window functions give both in one pass. LAG(hba1c) OVER (PARTITION BY patient_id ORDER BY test_date) puts the previous reading beside every reading, and ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY test_date DESC) gives the latest reading the number 1. Filtering on rn = 1 leaves one row per patient carrying exactly the pair we want, and the comparison latest < previous keeps the improvers.
A patient with a single reading has previous_hba1c NULL; the comparison is unknown and the patient drops out, as the statement requires. An equal value fails <, so an unchanged result is not counted. Note the filter must be applied outside the subquery — window functions are evaluated after WHERE, so rn cannot be filtered at the same level.
Without windows: find each patient's latest date with a correlated MAX, the previous date with a MAX below it, and join the two readings. That probes the table several times per patient; the window version sorts each patient's readings once.
Reference solution (MySQL)
SELECT patient_id, previous_hba1c, latest_hba1c
FROM (
SELECT patient_id, hba1c AS latest_hba1c,
LAG(hba1c) OVER (PARTITION BY patient_id ORDER BY test_date) AS previous_hba1c,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY test_date DESC) AS rn
FROM HbA1cReading
) t
WHERE rn = 1 AND latest_hba1c < previous_hba1c
ORDER BY patient_idAnother way
SELECT l.patient_id, p.hba1c AS previous_hba1c, l.hba1c AS latest_hba1c
FROM HbA1cReading l
JOIN HbA1cReading p ON p.patient_id = l.patient_id
AND p.test_date = (SELECT MAX(test_date) FROM HbA1cReading x WHERE x.patient_id = l.patient_id AND x.test_date < l.test_date)
WHERE l.test_date = (SELECT MAX(test_date) FROM HbA1cReading y WHERE y.patient_id = l.patient_id)
AND l.hba1c < p.hba1c
ORDER BY l.patient_id← Ward Bed Occupancy on the Census Date · Medicines Below Their Reorder Level →