runtimetask Join fan-out & cardinality · medium
Debug: visits after joining observations
Return provider_id and visits_with_vitals: encounters that recorded at least one observation, per provider. The query counts observations instead of visits. Fix it.
what to return
- Columns
provider_idvisits_with_vitalsin this order- Rows
- 425 rows · any order
- Skill
- Repair the query’s logic and confirm the fix on changed data. Refresher: Multi-grain joins without fan-out →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result425 rows · any order
| provider_id | visits_with_vitals |
|---|---|
| 1 | 34 |
| 2 | 35 |
| 3 | 5 |
first 3 of 425 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
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.