Calculate Days Between Order and Shipping
Return each shipped order with the whole number of days from order_date to ship_date.
- Date analysis
- Type conversion
- NULL handling
- Filtering
- Sorting
Exercise brief
Understand the request
Fulfillment operations analyst The weekly carrier scorecard needs elapsed calendar days for every shipped order.
The weekly carrier scorecard needs elapsed calendar days for every shipped order. Return each shipped order with the whole number of days from order_date to ship_date.
Return
- Return order_id, customer_id, order_date, ship_date, days_to_ship in this exact left-to-right order.
Constraints
- Exclude rows whose ship_date is NULL.
- Use date arithmetic rather than subtracting formatted text.
- Order by order_id ascending.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATEship_dateDATEorder_totalDECIMALorder_timestampDATETIME
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Date difference = number of days between two dates. Each engine spells this differently.
Hint 2
SQLite: julianday(a) - julianday(b) returns a float number of days — CAST to INTEGER.
Hint 3
Always guard against NULL ship_date so unshipped orders are excluded (or show NULL).
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT order_id, customer_id, order_date, ship_date, CAST(julianday(ship_date) - julianday(order_date) AS INTEGER) AS days_to_ship FROM orders WHERE ship_date IS NOT NULL ORDER BY order_id;Why this works
Day-difference is the most common date task and the most engine-divergent. SQLite has no DATEDIFF, so you subtract julianday() values. Postgres lets you subtract dates directly (yields an integer). MySQL has DATEDIFF(end, start). SQL Server has DATEDIFF(DAY, start, end) — note the unit comes first and the argument order is reversed from MySQL.
Success check
Every shipped order appears once with the correct non-negative days_to_ship value in stable order.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| order_id | customer_id | order_date | ship_date | days_to_ship |
|---|---|---|---|---|
| 1 | 1 | 2024-01-15 | 2024-01-18 | 3 |
| 2 | 2 | 2024-01-20 | 2024-01-23 | 3 |
| 3 | 1 | 2024-02-10 | 2024-02-15 | 5 |
| 4 | 3 | 2024-02-25 | 2024-02-28 | 3 |
| 5 | 2 | 2024-03-05 | 2024-03-08 | 3 |
| 6 | 4 | 2024-03-15 | 2024-03-20 | 5 |
| 7 | 1 | 2024-04-01 | 2024-04-05 | 4 |
| 8 | 5 | 2024-04-10 | 2024-04-12 | 2 |
| 9 | 3 | 2024-05-05 | 2024-05-08 | 3 |
| 10 | 2 | 2024-05-20 | 2024-05-23 | 3 |
Previewing 10 of 20 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.