runtimetask Query plans & SARGable filters · hard
Latest failed deliveries without a sort
Create an index so the 20 most recent delivery_failed scans can be read in order without a temporary sort, then return event_id, package_id and event_at for them, newest first.
what to return
- Columns
event_idpackage_idevent_atin this order- Rows
- 20 rows · in the order asked
- Skill
- Use index order to avoid a temporary sort. Refresher: Indexes: observe a real access path →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result20 rows · in this order
| event_id | package_id | event_at |
|---|---|---|
| 100484 | 3327 | 2025-04-30 23:00:00 |
| 100455 | 1699 | 2025-04-30 21:16:00 |
| 100439 | 197 | 2025-04-30 20:43:00 |
first 3 of 20 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.
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.