01_delay_analysis.sql

-- ============================================================
--  DataCo Supply Chain  |  Analysis 01: What drives late delivery?
--  Trick: AVG(late_delivery_risk) = share of late orders (0/1 flag)
-- ============================================================
USE dataco;

-- Q1: Late % by shipping mode  --> the suspected driver
SELECT shipping_mode,
       COUNT(*)                              AS total_orders,
       SUM(late_delivery_risk)               AS late_orders,
       ROUND(AVG(late_delivery_risk)*100, 1) AS late_pct
FROM supply_chain
GROUP BY shipping_mode
ORDER BY late_pct DESC;

-- Q2: Late % by region  --> is geography a driver? (control)
SELECT order_region,
       COUNT(*)                              AS total_orders,
       ROUND(AVG(late_delivery_risk)*100, 1) AS late_pct
FROM supply_chain
GROUP BY order_region
ORDER BY late_pct DESC;

-- Q3: Late % by category, only meaningful volumes  --> is product a driver? (control)
SELECT category_name,
       COUNT(*)                              AS total_orders,
       ROUND(AVG(late_delivery_risk)*100, 1) AS late_pct
FROM supply_chain
GROUP BY category_name
HAVING COUNT(*) >= 500
ORDER BY late_pct DESC
LIMIT 10;

-- Q4: The mechanism -- promised days vs actual days, per shipping mode
SELECT shipping_mode,
       ROUND(AVG(days_shipment_scheduled), 2) AS avg_scheduled_days,
       ROUND(AVG(days_shipping_real), 2)      AS avg_real_days,
       ROUND(AVG(days_shipping_real) - AVG(days_shipment_scheduled), 2) AS gap_days
FROM supply_chain
GROUP BY shipping_mode
ORDER BY gap_days DESC;