Shipping SLA Breach (Slower Than 3 Days)
Return only shipped orders whose elapsed shipping time is greater than three days.
- CASE expressions
- Date analysis
- Type conversion
- Filtering
- Sorting
Exercise brief
Understand the request
Fulfillment quality manager The shipping SLA flags completed shipments taking more than three calendar days.
The shipping SLA flags completed shipments taking more than three calendar days. Return only shipped orders whose elapsed shipping time is greater than three days.
Return
- Return order_id, customer_id, order_date, ship_date, ship_days, sla_status in this exact left-to-right order.
Constraints
- Exclude NULL ship_date values.
- Do not classify exactly three days as a breach.
- Order worst breaches first, then order_id.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATEship_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
ship_days = day-difference between ship_date and order_date.
Hint 2
Filter to ship_days > 3 in WHERE to keep only breaches.
Hint 3
Order by ship_days DESC to surface the worst offenders first.
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 ship_days, CASE WHEN CAST(julianday(ship_date) - julianday(order_date) AS INTEGER) > 3 THEN 'Breach' ELSE 'On Time' END AS sla_status FROM orders WHERE CAST(julianday(ship_date) - julianday(order_date) AS INTEGER) > 3 ORDER BY ship_days DESC, order_id;Why this works
SLA monitoring filters on a computed date difference. Note you cannot reference the ship_days alias inside WHERE (it is not yet computed there), so the expression is repeated — or wrap it in a subquery/CTE. The day-difference function is the only engine-specific piece.
Success check
Only true breaches appear once with the correct ship_days and Breach label.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| order_id | customer_id | order_date | ship_date | ship_days | sla_status |
|---|---|---|---|---|---|
| 3 | 1 | 2024-02-10 | 2024-02-15 | 5 | Breach |
| 6 | 4 | 2024-03-15 | 2024-03-20 | 5 | Breach |
| 7 | 1 | 2024-04-01 | 2024-04-05 | 4 | Breach |
| 15 | 2 | 2024-09-01 | 2024-09-05 | 4 | Breach |
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.