runtimetask LAG / LEAD · medium
Back-to-back failed deliveries
Return package_id, attempt_number, previous_outcome and outcome for every attempt that failed (outcome other than 'delivered') directly after a failed attempt.
what to return
- Columns
package_idattempt_numberprevious_outcomeoutcomein this order- Rows
- 248 rows · any order
- Skill
- Find consecutive failures in an attempt sequence. Refresher: Compare rows with LAG →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result248 rows · any order
| package_id | attempt_number | previous_outcome | outcome |
|---|---|---|---|
| 60 | 2 | recipient_absent | address_issue |
| 110 | 2 | address_issue | recipient_absent |
| 284 | 2 | address_issue | recipient_absent |
first 3 of 248 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.
delivery_attempts6 columns · 12,700 rows
| attempt_idnumber · unique id | package_idnumber · links to packages | attempt_numbernumber | attempted_atdate & time | outcometext | driver_notetext |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2025-01-24 16:34:00 | delivered | NULL |
| 2 | 2 | 1 | 2025-01-24 00:31:00 | delivered | NULL |
| 3 | 3 | 1 | 2025-04-11 19:15:00 | delivered | NULL |
| 4 | 4 | 1 | 2025-03-10 22:37:00 | delivered | Handed to resident |
| 5 | 5 | 1 | 2025-04-09 18:01:00 | delivered | NULL |
| 6 | 6 | 1 | 2025-01-22 23:16:00 | delivered | NULL |
- attempt_idnumber · unique id
- 1
- package_idnumber · links to packages
- 1
- attempt_numbernumber
- 1
- attempted_atdate & time
- 2025-01-24 16:34:00
- outcometext
- delivered
- driver_notetext
- NULL
- attempt_idnumber · unique id
- 2
- package_idnumber · links to packages
- 2
- attempt_numbernumber
- 1
- attempted_atdate & time
- 2025-01-24 00:31:00
- outcometext
- delivered
- driver_notetext
- NULL
- attempt_idnumber · unique id
- 3
- package_idnumber · links to packages
- 3
- attempt_numbernumber
- 1
- attempted_atdate & time
- 2025-04-11 19:15:00
- outcometext
- delivered
- driver_notetext
- NULL
- attempt_idnumber · unique id
- 4
- package_idnumber · links to packages
- 4
- attempt_numbernumber
- 1
- attempted_atdate & time
- 2025-03-10 22:37:00
- outcometext
- delivered
- driver_notetext
- Handed to resident
- attempt_idnumber · unique id
- 5
- package_idnumber · links to packages
- 5
- attempt_numbernumber
- 1
- attempted_atdate & time
- 2025-04-09 18:01:00
- outcometext
- delivered
- driver_notetext
- NULL
- attempt_idnumber · unique id
- 6
- package_idnumber · links to packages
- 6
- attempt_numbernumber
- 1
- attempted_atdate & time
- 2025-01-22 23:16:00
- outcometext
- delivered
- driver_notetext
- NULL
first 6 of 12,700 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.