runtimetask IN / EXISTS / NOT EXISTS · hard
Has the whole shipment arrived?
For multi-package shipments created in February 2025, return shipment_id and packages for those where every package has a delivered scan.
what to return
- Columns
shipment_idpackagesin this order- Rows
- 508 rows · any order
- Skill
- Require every child to meet a condition. Refresher: Ask whether a row exists →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result508 rows · any order
| shipment_id | packages |
|---|---|
| 38 | 2 |
| 52 | 2 |
| 59 | 4 |
first 3 of 508 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.
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
packages8 columns · 11,505 rows
| package_idnumber · unique id | shipment_idnumber · links to shipments | tracking_numbertext | package_typetext | weight_kgdecimal | length_cmnumber | width_cmnumber | height_cmnumber |
|---|---|---|---|---|---|---|---|
| 1 | 1 | RT100007919B | box | 2.85 | 52 | 15 | 30 |
| 2 | 1 | RT100015838C | box | 1.1 | 69 | 45 | 24 |
| 3 | 2 | RT10 0023 757D | envelope | 0.49 | 32 | 24 | 2 |
| 4 | 3 | RT100031676E | box | 2.93 | 51 | 35 | 5 |
| 5 | 4 | RT100039595F | envelope | 0.38 | 32 | 24 | 3 |
| 6 | 5 | RT100047514G | box | 1.9 | 30 | 27 | 25 |
- package_idnumber · unique id
- 1
- shipment_idnumber · links to shipments
- 1
- tracking_numbertext
- RT100007919B
- package_typetext
- box
- weight_kgdecimal
- 2.85
- length_cmnumber
- 52
- width_cmnumber
- 15
- height_cmnumber
- 30
- package_idnumber · unique id
- 2
- shipment_idnumber · links to shipments
- 1
- tracking_numbertext
- RT100015838C
- package_typetext
- box
- weight_kgdecimal
- 1.1
- length_cmnumber
- 69
- width_cmnumber
- 45
- height_cmnumber
- 24
- package_idnumber · unique id
- 3
- shipment_idnumber · links to shipments
- 2
- tracking_numbertext
- RT10 0023 757D
- package_typetext
- envelope
- weight_kgdecimal
- 0.49
- length_cmnumber
- 32
- width_cmnumber
- 24
- height_cmnumber
- 2
- package_idnumber · unique id
- 4
- shipment_idnumber · links to shipments
- 3
- tracking_numbertext
- RT100031676E
- package_typetext
- box
- weight_kgdecimal
- 2.93
- length_cmnumber
- 51
- width_cmnumber
- 35
- height_cmnumber
- 5
- package_idnumber · unique id
- 5
- shipment_idnumber · links to shipments
- 4
- tracking_numbertext
- RT100039595F
- package_typetext
- envelope
- weight_kgdecimal
- 0.38
- length_cmnumber
- 32
- width_cmnumber
- 24
- height_cmnumber
- 3
- package_idnumber · unique id
- 6
- shipment_idnumber · links to shipments
- 5
- tracking_numbertext
- RT100047514G
- package_typetext
- box
- weight_kgdecimal
- 1.9
- length_cmnumber
- 30
- width_cmnumber
- 27
- height_cmnumber
- 25
first 6 of 11,505 rows · your queries use all of them
tracking_events9 columns · 100,515 rows
| event_idnumber · unique id | package_idnumber · links to packages | event_typetext | event_atdate & time | recorded_atdate & time | warehouse_idnumber · links to warehouses | event_sourcetext | exception_codetext | driver_idnumber |
|---|---|---|---|---|---|---|---|---|
| 1 | 6359 | created | 2025-01-01 00:03:46 | 2025-01-01 00:04:36 | NULL | manual | NULL | NULL |
| 2 | 10950 | created | 2025-01-01 00:33:12 | 2025-01-01 00:35:16 | NULL | manual | NULL | NULL |
| 3 | 2045 | created | 2025-01-01 00:35:00 | 2025-01-01 00:37:08 | NULL | manual | NULL | NULL |
| 4 | 5977 | created | 2025-01-01 00:47:34 | 2025-01-01 00:48:13 | NULL | manual | NULL | NULL |
| 5 | 4930 | created | 2025-01-01 00:55:09 | 2025-01-01 00:57:10 | NULL | manual | NULL | NULL |
| 6 | 6331 | created | 2025-01-01 01:34:23 | 2025-01-01 01:35:30 | NULL | manual | NULL | NULL |
- event_idnumber · unique id
- 1
- package_idnumber · links to packages
- 6359
- event_typetext
- created
- event_atdate & time
- 2025-01-01 00:03:46
- recorded_atdate & time
- 2025-01-01 00:04:36
- warehouse_idnumber · links to warehouses
- NULL
- event_sourcetext
- manual
- exception_codetext
- NULL
- driver_idnumber
- NULL
- event_idnumber · unique id
- 2
- package_idnumber · links to packages
- 10950
- event_typetext
- created
- event_atdate & time
- 2025-01-01 00:33:12
- recorded_atdate & time
- 2025-01-01 00:35:16
- warehouse_idnumber · links to warehouses
- NULL
- event_sourcetext
- manual
- exception_codetext
- NULL
- driver_idnumber
- NULL
- event_idnumber · unique id
- 3
- package_idnumber · links to packages
- 2045
- event_typetext
- created
- event_atdate & time
- 2025-01-01 00:35:00
- recorded_atdate & time
- 2025-01-01 00:37:08
- warehouse_idnumber · links to warehouses
- NULL
- event_sourcetext
- manual
- exception_codetext
- NULL
- driver_idnumber
- NULL
- event_idnumber · unique id
- 4
- package_idnumber · links to packages
- 5977
- event_typetext
- created
- event_atdate & time
- 2025-01-01 00:47:34
- recorded_atdate & time
- 2025-01-01 00:48:13
- warehouse_idnumber · links to warehouses
- NULL
- event_sourcetext
- manual
- exception_codetext
- NULL
- driver_idnumber
- NULL
- event_idnumber · unique id
- 5
- package_idnumber · links to packages
- 4930
- event_typetext
- created
- event_atdate & time
- 2025-01-01 00:55:09
- recorded_atdate & time
- 2025-01-01 00:57:10
- warehouse_idnumber · links to warehouses
- NULL
- event_sourcetext
- manual
- exception_codetext
- NULL
- driver_idnumber
- NULL
- event_idnumber · unique id
- 6
- package_idnumber · links to packages
- 6331
- event_typetext
- created
- event_atdate & time
- 2025-01-01 01:34:23
- recorded_atdate & time
- 2025-01-01 01:35:30
- warehouse_idnumber · links to warehouses
- NULL
- event_sourcetext
- manual
- exception_codetext
- NULL
- driver_idnumber
- NULL
first 6 of 100,515 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.