Diabetic Patients Whose Latest HbA1c Improved

Medium
213.

Diabetic Patients Whose Latest HbA1c Improved

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

ColumnType
patient_idint
test_datedate
hba1cdecimal

Primary key (together): patient_id, test_date.

One row per patient per test day; hba1c is a percentage with one decimal.

Example

The rows must come back in the order shown.

HbA1cReading

patient_idtest_datehba1c
12024-09-108.4
12024-12-127.9
12025-03-157.1
22024-10-017
22025-01-057.6
32025-02-206.8
42024-11-119.2
42025-02-149.2
52024-08-086.4
52025-01-098.1
1 more row

Output

patient_idprevious_hba1clatest_hba1c
17.97.1
58.17.5
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.

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

ColumnType
patient_idint
test_datedate
hba1cdecimal

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_idtest_datehba1c
12024-09-108.4
12024-12-127.9
12025-03-157.1
22024-10-017
22025-01-057.6
32025-02-206.8
42024-11-119.2
42025-02-149.2
52024-08-086.4
52025-01-098.1
52025-04-027.5

Output

patient_idprevious_hba1clatest_hba1c
17.97.1
58.17.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_id

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