03_logistics_performance.sql

-- Create base view
CREATE TEMP VIEW delivery_performance AS
WITH delivery_metrics AS (
	SELECT o.order_id, o.customer_id, c.customer_state, o.order_purchase_timestamp,
		   o.order_delivered_customer_date, o.order_estimated_delivery_date,
		   o.order_delivered_customer_date::date - o.order_purchase_timestamp::date AS actual_delivery_days,
		   o.order_estimated_delivery_date::date - o.order_purchase_timestamp::date AS estimated_delivery_days,
		   o.order_delivered_customer_date::date - o.order_estimated_delivery_date::date AS delay_days,
		   r.review_score
	FROM delivered_orders o
	LEFT JOIN customers c
	ON o.customer_id = c.customer_id
	LEFT JOIN order_reviews r
	ON o.order_id = r.order_id
	WHERE order_delivered_customer_date IS NOT NULL
)
SELECT *,
	   CASE
		   WHEN delay_days <= 0 THEN 'On-time / Early'
		   WHEN delay_days BETWEEN 1 AND 3 THEN '1-3 days late'
		   WHEN delay_days BETWEEN 4 AND 7 THEN '4-7 days late'
		   ELSE '8+ days late'
	   END AS delivery_status
FROM delivery_metrics;


-- Overall Delay Distribution
SELECT COUNT(*) AS total_delivered_orders,
	   ROUND(AVG(CASE WHEN delay_days > 0 THEN delay_days END), 2) AS avg_days_delayed,
	   ROUND(100.0 * SUM(CASE WHEN delay_days > 0 THEN 1 ELSE 0 END) / COUNT(*), 2) AS delay_orders_pct
FROM delivery_performance;

-- Delay Distribution by State
SELECT customer_state, COUNT(*) AS total_delivered_orders,
	   ROUND(AVG(CASE WHEN delay_days > 0 THEN delay_days END), 2) AS avg_days_delayed,
	   ROUND(100.0 * SUM(CASE WHEN delay_days > 0 THEN 1 ELSE 0 END) / COUNT(*), 2) AS delay_orders_pct
FROM delivery_performance
GROUP BY customer_state
ORDER BY avg_days_delayed DESC;


-- Review Score by Delivery Status
SELECT delivery_status, COUNT(*) AS number_of_orders,
	   ROUND(AVG(review_score), 2) AS avg_review_score
FROM delivery_performance
GROUP BY delivery_status
ORDER BY avg_review_score DESC;