runtimetask LEFT JOIN · medium
Debug: keep carriers without next-day shipments
Return carrier_id, carrier_name and next_day_shipments (service_level_id 4) for every carrier, 0 when none. The current query drops carriers with no next-day shipments.
what to return
- Columns
carrier_idcarrier_namenext_day_shipmentsin this order- Rows
- 14 rows · any order
- Skill
- Repair the query’s logic and confirm the fix on changed data. Refresher: ON versus WHERE in a LEFT JOIN →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result14 rows · any order
| carrier_id | carrier_name | next_day_shipments |
|---|---|---|
| 1 | Swiftline Parcel | 212 |
| 2 | NorthStar Express | 239 |
| 3 | Bluewater Logistics | 0 |
first 3 of 14 rows
data you can query
Logistics 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.
carriers4 columns · 14 rows
| carrier_idnumber · unique id | carrier_nametext | carrier_typetext | on_time_target_pctnumber |
|---|---|---|---|
| 1 | Swiftline Parcel | parcel | 95 |
| 2 | NorthStar Express | parcel | 96 |
| 3 | Bluewater Logistics | regional | 92 |
| 4 | Prairie Courier | regional | 90 |
| 5 | Granite Freight | ltl | 88 |
| 6 | Metro Dash | courier | 97 |
- carrier_idnumber · unique id
- 1
- carrier_nametext
- Swiftline Parcel
- carrier_typetext
- parcel
- on_time_target_pctnumber
- 95
- carrier_idnumber · unique id
- 2
- carrier_nametext
- NorthStar Express
- carrier_typetext
- parcel
- on_time_target_pctnumber
- 96
- carrier_idnumber · unique id
- 3
- carrier_nametext
- Bluewater Logistics
- carrier_typetext
- regional
- on_time_target_pctnumber
- 92
- carrier_idnumber · unique id
- 4
- carrier_nametext
- Prairie Courier
- carrier_typetext
- regional
- on_time_target_pctnumber
- 90
- carrier_idnumber · unique id
- 5
- carrier_nametext
- Granite Freight
- carrier_typetext
- ltl
- on_time_target_pctnumber
- 88
- carrier_idnumber · unique id
- 6
- carrier_nametext
- Metro Dash
- carrier_typetext
- courier
- on_time_target_pctnumber
- 97
first 6 of 14 rows · your queries use all of them
shipments12 columns · 8,200 rows
| shipment_idnumber · unique id | order_referencetext | shipper_nametext | origin_warehouse_idnumber · links to warehouses | destination_citytext | destination_statetext | destination_postal_codetext | service_level_idnumber · links to service_levels | carrier_idnumber · links to carriers | created_atdate & time | promised_delivery_datedate | declared_valuedecimal |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ORD-643450 | Delta Robotics | 4 | Orlando | FL | 33457 | 3 | 3 | 2025-01-19 21:43:15 | 2025-01-22 | 44.17 |
| 2 | ORD-102115 | Crest Outfitters | 3 | Raleigh | NC | 27481 | 7 | 1 | 2025-04-09 00:16:31 | 2025-04-15 | 114.7 |
| 3 | ORD-926480 | Arbor Systems | 3 | Charlotte | NC | 27558 | 7 | 7 | 2025-03-06 19:19:35 | 2025-03-13 | 50.37 |
| 4 | ORD-651018 | Timber Robotics | 6 | Miami | FL | 33122 | 1 | 12 | 2025-04-05 00:31:02 | 2025-04-11 | 1181.62 |
| 5 | ORD-901762 | APEX ANALYTICS | 4 | San Diego | CA | 97949 | 2 | 3 | 2025-01-20 04:30:44 | 2025-01-29 | 91.96 |
| 6 | ORD-832022 | Nimbus Outfitters | 3 | New York | NY | 10050 | 6 | 5 | 2025-04-03 15:09:38 | 2025-04-11 | 166.67 |
- shipment_idnumber · unique id
- 1
- order_referencetext
- ORD-643450
- shipper_nametext
- Delta Robotics
- origin_warehouse_idnumber · links to warehouses
- 4
- destination_citytext
- Orlando
- destination_statetext
- FL
- destination_postal_codetext
- 33457
- service_level_idnumber · links to service_levels
- 3
- carrier_idnumber · links to carriers
- 3
- created_atdate & time
- 2025-01-19 21:43:15
- promised_delivery_datedate
- 2025-01-22
- declared_valuedecimal
- 44.17
- shipment_idnumber · unique id
- 2
- order_referencetext
- ORD-102115
- shipper_nametext
- Crest Outfitters
- origin_warehouse_idnumber · links to warehouses
- 3
- destination_citytext
- Raleigh
- destination_statetext
- NC
- destination_postal_codetext
- 27481
- service_level_idnumber · links to service_levels
- 7
- carrier_idnumber · links to carriers
- 1
- created_atdate & time
- 2025-04-09 00:16:31
- promised_delivery_datedate
- 2025-04-15
- declared_valuedecimal
- 114.7
- shipment_idnumber · unique id
- 3
- order_referencetext
- ORD-926480
- shipper_nametext
- Arbor Systems
- origin_warehouse_idnumber · links to warehouses
- 3
- destination_citytext
- Charlotte
- destination_statetext
- NC
- destination_postal_codetext
- 27558
- service_level_idnumber · links to service_levels
- 7
- carrier_idnumber · links to carriers
- 7
- created_atdate & time
- 2025-03-06 19:19:35
- promised_delivery_datedate
- 2025-03-13
- declared_valuedecimal
- 50.37
- shipment_idnumber · unique id
- 4
- order_referencetext
- ORD-651018
- shipper_nametext
- Timber Robotics
- origin_warehouse_idnumber · links to warehouses
- 6
- destination_citytext
- Miami
- destination_statetext
- FL
- destination_postal_codetext
- 33122
- service_level_idnumber · links to service_levels
- 1
- carrier_idnumber · links to carriers
- 12
- created_atdate & time
- 2025-04-05 00:31:02
- promised_delivery_datedate
- 2025-04-11
- declared_valuedecimal
- 1181.62
- shipment_idnumber · unique id
- 5
- order_referencetext
- ORD-901762
- shipper_nametext
- APEX ANALYTICS
- origin_warehouse_idnumber · links to warehouses
- 4
- destination_citytext
- San Diego
- destination_statetext
- CA
- destination_postal_codetext
- 97949
- service_level_idnumber · links to service_levels
- 2
- carrier_idnumber · links to carriers
- 3
- created_atdate & time
- 2025-01-20 04:30:44
- promised_delivery_datedate
- 2025-01-29
- declared_valuedecimal
- 91.96
- shipment_idnumber · unique id
- 6
- order_referencetext
- ORD-832022
- shipper_nametext
- Nimbus Outfitters
- origin_warehouse_idnumber · links to warehouses
- 3
- destination_citytext
- New York
- destination_statetext
- NY
- destination_postal_codetext
- 10050
- service_level_idnumber · links to service_levels
- 6
- carrier_idnumber · links to carriers
- 5
- created_atdate & time
- 2025-04-03 15:09:38
- promised_delivery_datedate
- 2025-04-11
- declared_valuedecimal
- 166.67
first 6 of 8,200 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.