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
Interview 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.
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: