#!/home/rosy/bin/mariadb

-- Step 1: First, let's see the raw delivery data for 2023
-- Each row is one day at one warehouse
SELECT warehouse_id, warehouse_name, report_date, total_shipments
FROM daily_delivery_metrics
WHERE YEAR(report_date) = 2023
LIMIT 5;

-- Step 2: Now let's GROUP BY warehouse to get totals
-- SUM adds up all the daily shipments for each warehouse
SELECT 
    warehouse_id,
    warehouse_name,
    SUM(total_shipments) AS total_deliveries
FROM daily_delivery_metrics
WHERE YEAR(report_date) = 2023
GROUP BY warehouse_id, warehouse_name
ORDER BY total_deliveries DESC;

-- Step 3: Find just the TOP warehouse's count
-- We'll need this number to compare everyone against
SELECT SUM(total_shipments) AS top_deliveries
FROM daily_delivery_metrics
WHERE YEAR(report_date) = 2023
GROUP BY warehouse_id
ORDER BY top_deliveries DESC
LIMIT 1;

-- Step 4: Now the full query using CTEs (WITH clause)
-- CTEs are like named "temporary results" you can reuse
WITH warehouse_totals AS (
    -- This CTE holds each warehouse's total
    SELECT 
        warehouse_id,
        warehouse_name,
        SUM(total_shipments) AS total_deliveries
    FROM daily_delivery_metrics
    WHERE YEAR(report_date) = 2023
    GROUP BY warehouse_id, warehouse_name
),
top_warehouse AS (
    -- This CTE holds just the top number
    SELECT total_deliveries
    FROM warehouse_totals
    ORDER BY total_deliveries DESC
    LIMIT 1
)
-- CROSS JOIN attaches the top number to EVERY row
-- so each warehouse can calculate its % difference
SELECT 
    wt.warehouse_name,
    wt.total_deliveries,
    tw.total_deliveries AS top_deliveries,
    ROUND(
        (wt.total_deliveries - tw.total_deliveries) / tw.total_deliveries * 100, 
        1
    ) AS pct_diff_from_top
FROM warehouse_totals wt
CROSS JOIN top_warehouse tw
ORDER BY wt.total_deliveries DESC;
