runtimetask Correlated subqueries · medium
Each patient’s latest systolic reading
For patients 1–400 with at least one known systolic reading (code BP_SYS), return patient_id, observed_at and value of the latest one (ties by highest observation_id).
what to return
- Columns
patient_idobserved_atvaluein this order- Rows
- 372 rows · any order
- Skill
- Pick the most recent measurement per entity. Refresher: Subqueries →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result372 rows · any order
| patient_id | observed_at | value |
|---|---|---|
| 1 | 2025-04-13 19:26:00 | 129 |
| 2 | 2025-05-28 11:20:00 | 125 |
| 3 | 2024-11-24 12:22:00 | 111 |
first 3 of 372 rows
data you can query
Healthcare dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
observations8 columns · 39,529 rows
| observation_idnumber · unique id | patient_idnumber · links to patients | encounter_idnumber · links to encounters | observed_atdate & time | codetext | descriptiontext | valuedecimal | unittext |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 2022-08-07 13:18:00 | BP_SYS | Systolic blood pressure | 126 | mmHg |
| 2 | 1 | 1 | 2022-08-07 13:05:00 | BP_DIA | Diastolic blood pressure | 79 | mmHg |
| 3 | 1 | 2 | 2022-10-12 11:50:00 | A1C | Hemoglobin A1c | 5.96 | % |
| 4 | 1 | 3 | 2023-05-12 12:02:00 | A1C | Hemoglobin A1c | 6.06 | % |
| 5 | 1 | 4 | 2023-12-17 08:40:00 | BP_SYS | Systolic blood pressure | 122 | mmHg |
| 6 | 1 | 4 | 2023-12-17 08:33:00 | BP_DIA | Diastolic blood pressure | 71 | mmHg |
- observation_idnumber · unique id
- 1
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 1
- observed_atdate & time
- 2022-08-07 13:18:00
- codetext
- BP_SYS
- descriptiontext
- Systolic blood pressure
- valuedecimal
- 126
- unittext
- mmHg
- observation_idnumber · unique id
- 2
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 1
- observed_atdate & time
- 2022-08-07 13:05:00
- codetext
- BP_DIA
- descriptiontext
- Diastolic blood pressure
- valuedecimal
- 79
- unittext
- mmHg
- observation_idnumber · unique id
- 3
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 2
- observed_atdate & time
- 2022-10-12 11:50:00
- codetext
- A1C
- descriptiontext
- Hemoglobin A1c
- valuedecimal
- 5.96
- unittext
- %
- observation_idnumber · unique id
- 4
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 3
- observed_atdate & time
- 2023-05-12 12:02:00
- codetext
- A1C
- descriptiontext
- Hemoglobin A1c
- valuedecimal
- 6.06
- unittext
- %
- observation_idnumber · unique id
- 5
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 4
- observed_atdate & time
- 2023-12-17 08:40:00
- codetext
- BP_SYS
- descriptiontext
- Systolic blood pressure
- valuedecimal
- 122
- unittext
- mmHg
- observation_idnumber · unique id
- 6
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 4
- observed_atdate & time
- 2023-12-17 08:33:00
- codetext
- BP_DIA
- descriptiontext
- Diastolic blood pressure
- valuedecimal
- 71
- unittext
- mmHg
first 6 of 39,529 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.