HardLogistics12 min

Age of in-transit shipments

Challenge

Show how many days each in-transit shipment has been open as of 15 March 2025.

Business context

The control tower ages open freight against a fixed as-of date so the morning report does not change just because the query runs later.

Instructions

Return id, destination_city, and days_open. days_open is the integer day difference from shipped_on to 2025-03-15. Keep status in_transit. Sort by days_open descending, then id.

Schema

warehouses

id INTEGER · primary key
name TEXT
city TEXT

shipments

id INTEGER · primary key
warehouse_id INTEGER
destination_city TEXT
status TEXT · delivered, in_transit, cancelled
shipped_on TEXT
delivered_on TEXT · nullable

Sample rows

warehouses
id	name	city
1	Berlin Hub	Berlin
2	Lagos DC	Lagos
3	Rotterdam Port	Rotterdam

shipments
id	warehouse_id	destination_city	status	shipped_on	delivered_on
1	1	Paris	delivered	2025-01-04	2025-01-06
2	1	Warsaw	in_transit	2025-02-11	NULL
3	2	Accra	in_transit	2025-02-12	NULL

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Top vendor in each category