The operations team wants to identify warehouses that have completed all their shipments for May 2026.
Each row in the shipments table represents a parcel dispatched from a warehouse. The warehouse team updates its status to 'shipped', 'delivered', or 'cancelled', but these updates are not always reliable. A parcel may still be marked 'shipped' even after it has been delivered.
The carrier records the actual delivery time in delivered_at, making it the reliable indicator of whether a shipment has arrived.
A warehouse is ready to close the month when all its live shipments dispatched during May 2026 have been delivered.
The following rules apply:
01Live shipment: Any shipment whose status is not 'cancelled'. A missing (NULL) status also counts as live.
02May shipment: A shipment with dispatched_on between May 1 and May 31, 2026, inclusive.
03Delivered shipment: A shipment with a recorded delivered_at, regardless of its status.
Prepare a report showing each warehouse ready to close May, the number of its live May shipments, and the latest delivery timestamp among them.
Warehouses with no live May shipments should not appear.
Ordering: Sort by warehouse_id in ascending order.