Late Shipments
shipments stores a promised_date and the actual delivered_date (both YYYY-MM-DD). Shipments still in transit have a NULL delivered_date and should be ignored.
Find delivered shipments that arrived after their promised date, and compute days_late as a whole number of days.
Return columns shipment_id, days_late. Order by days_late descending, then shipment_id ascending.
Schema
CREATE TABLE shipments ( shipment_id INTEGER PRIMARY KEY, carrier TEXT NOT NULL, promised_date TEXT NOT NULL, delivered_date TEXT );
Sample data
shipments
| shipment_id | carrier | promised_date | delivered_date |
|---|---|---|---|
| 1 | Aramex | 2026-03-01 | 2026-03-01 |
| 2 | DHL | 2026-03-02 | 2026-03-05 |
| 3 | FedEx | 2026-03-03 | 2026-03-02 |
| 4 | Aramex | 2026-03-04 | NULL |
| 5 | DHL | 2026-03-05 | 2026-03-12 |
| 6 | FedEx | 2026-03-06 | 2026-03-07 |
Hint 1
julianday() turns a date into a day number, so subtracting two of them gives the gap in days.
Hint 2
Date strings in YYYY-MM-DD form compare correctly as text, but arithmetic across month and year ends needs real date functions.
Good practice for: Data Analyst, Business Analyst, Backend Developer
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.