-- Supply Chain Delay Analysis Queries
USE supply_chain_db;

-- =========================================================================
-- SECTION A: Simple SQL Queries (Basic Data Inspection)
-- =========================================================================

-- 1. View first 5 customers in the database
SELECT customer_id, customer_fname, customer_lname, customer_city FROM customers LIMIT 5;

-- 2. Count total number of orders in the system
SELECT COUNT(*) AS total_orders_count FROM orders;

-- 3. Filter orders to find 10 delayed shipments
SELECT order_id, order_date, shipping_mode, late_delivery_risk 
FROM orders 
WHERE late_delivery_risk = 1 
LIMIT 10;

-- 4. Count customers grouped by segment (Consumer, Corporate, etc.)
SELECT customer_segment, COUNT(*) AS customer_count 
FROM customers 
GROUP BY customer_segment;

-- 5. List the top 5 most expensive products
SELECT product_name, product_price 
FROM products 
ORDER BY product_price DESC 
LIMIT 5;


-- =========================================================================
-- SECTION B: Advanced Supply Chain Delay Analysis Queries
-- =========================================================================

-- 1. Late Delivery Rate per Shipping Mode
-- This query helps understand if certain shipping modes (e.g. Same Day, First Class) have a higher risk of delay.
SELECT 
    shipping_mode,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN late_delivery_risk = 1 THEN 1 ELSE 0 END) AS delayed_orders,
    ROUND(SUM(CASE WHEN late_delivery_risk = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS late_delivery_rate_percent,
    ROUND(AVG(days_for_shipping_real), 2) AS avg_actual_shipping_days,
    ROUND(AVG(days_for_shipping_scheduled), 2) AS avg_scheduled_shipping_days
FROM orders
GROUP BY shipping_mode
ORDER BY late_delivery_rate_percent DESC;


-- 2. Delayed Orders by Customer Segment and Market Region
-- This identifies which regions and customer types experience the most shipping bottlenecks.
SELECT 
    c.customer_segment,
    o.market,
    o.order_region,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN o.late_delivery_risk = 1 THEN 1 ELSE 0 END) AS delayed_orders,
    ROUND(SUM(CASE WHEN o.late_delivery_risk = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS late_delivery_rate_percent
FROM orders o
JOIN customers c ON o.order_customer_id = c.customer_id
GROUP BY c.customer_segment, o.market, o.order_region
HAVING total_orders > 100
ORDER BY late_delivery_rate_percent DESC
LIMIT 15;


-- 3. Product Categories with the Highest Delays
-- This highlights if specific product categories suffer from supply chain bottlenecks.
SELECT 
    cat.category_name,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(CASE WHEN o.late_delivery_risk = 1 THEN 1 ELSE 0 END) AS delayed_orders,
    ROUND(SUM(CASE WHEN o.late_delivery_risk = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(DISTINCT o.order_id), 2) AS late_delivery_rate_percent
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.order_item_cardprod_id = p.product_card_id
JOIN categories cat ON p.product_category_id = cat.category_id
GROUP BY cat.category_name
HAVING total_orders > 50
ORDER BY late_delivery_rate_percent DESC
LIMIT 15;


-- 4. Financial Impact: Profit Loss by Delivery Status
-- This query computes total sales and profits across different delivery statuses (e.g., Late delivery, Shipping canceled).
SELECT 
    o.delivery_status,
    COUNT(DISTINCT o.order_id) AS total_orders,
    ROUND(SUM(oi.sales), 2) AS total_sales,
    ROUND(SUM(oi.benefit_per_order), 2) AS total_profit,
    ROUND(AVG(oi.order_item_discount_rate) * 100, 2) AS avg_discount_rate_percent
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.delivery_status
ORDER BY total_profit DESC;


-- 5. Top 10 Countries showing the highest Delay Risk
SELECT 
    order_country,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN late_delivery_risk = 1 THEN 1 ELSE 0 END) AS delayed_orders,
    ROUND(SUM(CASE WHEN late_delivery_risk = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS late_delivery_rate_percent
FROM orders
GROUP BY order_country
HAVING total_orders > 50
ORDER BY late_delivery_rate_percent DESC
LIMIT 10;