runtimetask Missing relationships / anti-joins · easy
Providers with no encounters
Return provider_id, full_name and specialty for every provider who has no encounters.
what to return
- Columns
provider_idfull_namespecialtyin this order- Rows
- 29 rows · any order
- Skill
- Find rows with no related rows. Refresher: Ask whether a row exists →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result29 rows · any order
| provider_id | full_name | specialty |
|---|---|---|
| 115 | Mark Ahmed | Orthopedics |
| 137 | Lucia Robinson | Cardiology |
| 178 | Nia Abbott | Orthopedics |
first 3 of 29 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.
providers6 columns · 460 rows
| provider_idnumber · unique id | full_nametext | specialtytext | clinic_nametext | citytext | statetext |
|---|---|---|---|---|---|
| 1 | Amara Mehta | Family Medicine | Providence Family Practice | Providence | RI |
| 2 | Alice Hill | Family Medicine | Nimbus Health Center | Atlanta | GA |
| 3 | Ryan Shah | Nephrology | Cleveland Community Clinic | Cleveland | OH |
| 4 | Sofia Lee | Family Medicine | Albuquerque Community Clinic | Albuquerque | NM |
| 5 | Joseph Howard | Family Medicine | Prism Health Center | New York | NY |
| 6 | Anika Scott | Pediatrics | Solar Health Center | Indianapolis | IN |
- provider_idnumber · unique id
- 1
- full_nametext
- Amara Mehta
- specialtytext
- Family Medicine
- clinic_nametext
- Providence Family Practice
- citytext
- Providence
- statetext
- RI
- provider_idnumber · unique id
- 2
- full_nametext
- Alice Hill
- specialtytext
- Family Medicine
- clinic_nametext
- Nimbus Health Center
- citytext
- Atlanta
- statetext
- GA
- provider_idnumber · unique id
- 3
- full_nametext
- Ryan Shah
- specialtytext
- Nephrology
- clinic_nametext
- Cleveland Community Clinic
- citytext
- Cleveland
- statetext
- OH
- provider_idnumber · unique id
- 4
- full_nametext
- Sofia Lee
- specialtytext
- Family Medicine
- clinic_nametext
- Albuquerque Community Clinic
- citytext
- Albuquerque
- statetext
- NM
- provider_idnumber · unique id
- 5
- full_nametext
- Joseph Howard
- specialtytext
- Family Medicine
- clinic_nametext
- Prism Health Center
- citytext
- New York
- statetext
- NY
- provider_idnumber · unique id
- 6
- full_nametext
- Anika Scott
- specialtytext
- Pediatrics
- clinic_nametext
- Solar Health Center
- citytext
- Indianapolis
- statetext
- IN
first 6 of 460 rows · your queries use all of them
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.