Latest Status per Shipment
shipment_events is an append-only tracking log. For each shipment, find its most recent event: the one with the latest event_time. If two events of a shipment have the same event_time, the one with the larger event_id is the latest.
Return columns shipment_id, status, event_time. Row order does not matter.
Schema
CREATE TABLE shipment_events ( event_id INTEGER PRIMARY KEY, shipment_id TEXT NOT NULL, status TEXT NOT NULL, event_time TEXT NOT NULL );
Sample data
shipment_events
| event_id | shipment_id | status | event_time |
|---|---|---|---|
| 1 | SH-1001 | picked_up | 2026-05-01 08:00:00 |
| 2 | SH-1001 | in_transit | 2026-05-01 14:30:00 |
| 3 | SH-1002 | picked_up | 2026-05-01 09:10:00 |
| 4 | SH-1001 | out_for_delivery | 2026-05-02 07:45:00 |
| 5 | SH-1003 | picked_up | 2026-05-02 10:00:00 |
| 6 | SH-1002 | in_transit | 2026-05-02 11:20:00 |
| 7 | SH-1001 | delivered | 2026-05-02 12:05:00 |
Hint 1
Number the events of each shipment from newest to oldest and keep number 1.
Hint 2
event_id order is not the same as time order. A low id can still have the latest time.
Good practice for: Data Engineer, Backend Developer, Data Analyst
SQL (SQLite)
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.