runtimetask Gaps & islands · hard
Continuous medication coverage
For patients 1–200, merge each patient’s fills of the same medication_name into continuous coverage periods: a fill continues the period when its start_date is no later than the day after the latest stop_date so far. Return patient_id, medication_name, covered_from, covered_to and fills.
what to return
- Columns
patient_idmedication_namecovered_fromcovered_tofillsin this order- Rows
- 757 rows · any order
- Skill
- Build continuous coverage periods from overlapping fills. Refresher: Sessionize an event stream →
expected result
Your query should return exactly these columns and values. Run it and compare.
| patient_id | medication_name | covered_from | covered_to | fills |
|---|---|---|---|---|
| 1 | Cetirizine 10 mg tablet | 2022-08-07 | 2022-09-06 | 1 |
| 1 | Metformin 500 mg tablet | 2022-10-12 | 2023-09-21 | 2 |
| 1 | Metformin 500 mg tablet | 2023-09-23 | 2024-03-21 | 1 |
first 3 of 757 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.
medications8 columns · 19,275 rows
| medication_idnumber · unique id | patient_idnumber · links to patients | encounter_idnumber · links to encounters | medication_nametext | start_datedate | stop_datedate | days_supplynumber | costdecimal |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | Cetirizine 10 mg tablet | 2022-08-07 | 2022-09-06 | 30 | 18.83 |
| 2 | 1 | 2 | Metformin 500 mg tablet | 2022-10-12 | 2023-04-10 | 180 | 13.33 |
| 3 | 1 | 2 | Metformin 500 mg tablet | 2023-03-25 | 2023-09-21 | 180 | 14.93 |
| 4 | 1 | 2 | Metformin 500 mg tablet | 2023-09-23 | 2024-03-21 | 180 | 14.36 |
| 5 | 2 | 9 | Albuterol 90 mcg inhaler | 2022-09-03 | 2023-03-02 | 180 | 71.18 |
| 6 | 2 | 9 | Albuterol 90 mcg inhaler | 2023-03-05 | 2023-09-01 | 180 | 70.02 |
- medication_idnumber · unique id
- 1
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 1
- medication_nametext
- Cetirizine 10 mg tablet
- start_datedate
- 2022-08-07
- stop_datedate
- 2022-09-06
- days_supplynumber
- 30
- costdecimal
- 18.83
- medication_idnumber · unique id
- 2
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 2
- medication_nametext
- Metformin 500 mg tablet
- start_datedate
- 2022-10-12
- stop_datedate
- 2023-04-10
- days_supplynumber
- 180
- costdecimal
- 13.33
- medication_idnumber · unique id
- 3
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 2
- medication_nametext
- Metformin 500 mg tablet
- start_datedate
- 2023-03-25
- stop_datedate
- 2023-09-21
- days_supplynumber
- 180
- costdecimal
- 14.93
- medication_idnumber · unique id
- 4
- patient_idnumber · links to patients
- 1
- encounter_idnumber · links to encounters
- 2
- medication_nametext
- Metformin 500 mg tablet
- start_datedate
- 2023-09-23
- stop_datedate
- 2024-03-21
- days_supplynumber
- 180
- costdecimal
- 14.36
- medication_idnumber · unique id
- 5
- patient_idnumber · links to patients
- 2
- encounter_idnumber · links to encounters
- 9
- medication_nametext
- Albuterol 90 mcg inhaler
- start_datedate
- 2022-09-03
- stop_datedate
- 2023-03-02
- days_supplynumber
- 180
- costdecimal
- 71.18
- medication_idnumber · unique id
- 6
- patient_idnumber · links to patients
- 2
- encounter_idnumber · links to encounters
- 9
- medication_nametext
- Albuterol 90 mcg inhaler
- start_datedate
- 2023-03-05
- stop_datedate
- 2023-09-01
- days_supplynumber
- 180
- costdecimal
- 70.02
first 6 of 19,275 rows · your queries use all of them
Hints and the answer are always available. Using them counts the question as solved with help.