runtimetask Validation & types · hard
Overlapping prescription fills
For patients 1–300, return patient_id, medication_name, first_id and second_id for every pair of fills of the same drug for the same patient whose supply intervals overlap. Intervals are [start_date, stop_date); list each pair once with first_id < second_id.
what to return
- Columns
patient_idmedication_namefirst_idsecond_idin this order- Rows
- 441 rows · any order
- Skill
- Detect overlapping intervals within a group. Refresher: Product validity with LEAD & half-open intervals →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result441 rows · any order
| patient_id | medication_name | first_id | second_id |
|---|---|---|---|
| 1 | Metformin 500 mg tablet | 2 | 3 |
| 2 | Albuterol 90 mcg inhaler | 6 | 7 |
| 2 | Albuterol 90 mcg inhaler | 7 | 8 |
first 3 of 441 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
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.