runtimetask Self joins · medium
Encounters that overlap in time
Return patient_id, first_encounter, second_encounter (first_encounter < second_encounter) and overlap_minutes for every pair of the same patient’s encounters whose [start_at, end_at) intervals overlap.
what to return
- Columns
patient_idfirst_encountersecond_encounteroverlap_minutesin this order- Rows
- 158 rows · any order
- Skill
- Detect overlapping intervals for the same entity. Refresher: Self-joins & referral relationships →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result158 rows · any order
| patient_id | first_encounter | second_encounter | overlap_minutes |
|---|---|---|---|
| 96 | 627 | 628 | 139 |
| 116 | 731 | 732 | 75 |
| 125 | 792 | 793 | 141 |
first 3 of 158 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.
encounters9 columns · 20,447 rows
| encounter_idnumber · unique id | patient_idnumber · links to patients | provider_idnumber · links to providers | encounter_classtext | start_atdate & time | end_atdate & time | reason_codetext | total_costdecimal | payer_paiddecimal |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 4 | ambulatory | 2022-08-07 13:00:00 | 2022-08-07 13:24:45 | T78.40XA | 175.6 | 159.38 |
| 2 | 1 | 4 | telehealth | 2022-10-12 11:15:00 | 2022-10-12 11:59:48 | E11.9 | 110.02 | 95.53 |
| 3 | 1 | 74 | telehealth | 2023-05-12 11:30:00 | 2023-05-12 12:13:27 | E11.9 | 83.68 | 74.77 |
| 4 | 1 | 349 | ambulatory | 2023-12-17 08:15:00 | 2023-12-17 08:39:33 | E11.9 | 198.23 | 187.3 |
| 5 | 1 | 4 | telehealth | 2024-05-09 16:45:00 | 2024-05-09 17:29:01 | E66.9 | 115.08 | 98.4 |
| 6 | 1 | 4 | ambulatory | 2024-09-07 13:00:00 | 2024-09-07 13:27:30 | E66.9 | 153.61 | 142.99 |
- encounter_idnumber · unique id
- 1
- patient_idnumber · links to patients
- 1
- provider_idnumber · links to providers
- 4
- encounter_classtext
- ambulatory
- start_atdate & time
- 2022-08-07 13:00:00
- end_atdate & time
- 2022-08-07 13:24:45
- reason_codetext
- T78.40XA
- total_costdecimal
- 175.6
- payer_paiddecimal
- 159.38
- encounter_idnumber · unique id
- 2
- patient_idnumber · links to patients
- 1
- provider_idnumber · links to providers
- 4
- encounter_classtext
- telehealth
- start_atdate & time
- 2022-10-12 11:15:00
- end_atdate & time
- 2022-10-12 11:59:48
- reason_codetext
- E11.9
- total_costdecimal
- 110.02
- payer_paiddecimal
- 95.53
- encounter_idnumber · unique id
- 3
- patient_idnumber · links to patients
- 1
- provider_idnumber · links to providers
- 74
- encounter_classtext
- telehealth
- start_atdate & time
- 2023-05-12 11:30:00
- end_atdate & time
- 2023-05-12 12:13:27
- reason_codetext
- E11.9
- total_costdecimal
- 83.68
- payer_paiddecimal
- 74.77
- encounter_idnumber · unique id
- 4
- patient_idnumber · links to patients
- 1
- provider_idnumber · links to providers
- 349
- encounter_classtext
- ambulatory
- start_atdate & time
- 2023-12-17 08:15:00
- end_atdate & time
- 2023-12-17 08:39:33
- reason_codetext
- E11.9
- total_costdecimal
- 198.23
- payer_paiddecimal
- 187.3
- encounter_idnumber · unique id
- 5
- patient_idnumber · links to patients
- 1
- provider_idnumber · links to providers
- 4
- encounter_classtext
- telehealth
- start_atdate & time
- 2024-05-09 16:45:00
- end_atdate & time
- 2024-05-09 17:29:01
- reason_codetext
- E66.9
- total_costdecimal
- 115.08
- payer_paiddecimal
- 98.4
- encounter_idnumber · unique id
- 6
- patient_idnumber · links to patients
- 1
- provider_idnumber · links to providers
- 4
- encounter_classtext
- ambulatory
- start_atdate & time
- 2024-09-07 13:00:00
- end_atdate & time
- 2024-09-07 13:27:30
- reason_codetext
- E66.9
- total_costdecimal
- 153.61
- payer_paiddecimal
- 142.99
first 6 of 20,447 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.