-- Compare all warehouses against the top performer in 2023
-- Shows percentage difference in deliveries from the leader

WITH warehouse_totals AS (
    -- Step 1: Sum up deliveries per warehouse for 2023
    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 (
    -- Step 2: Find the highest delivery count
    SELECT total_deliveries AS max_deliveries
    FROM warehouse_totals
    ORDER BY total_deliveries DESC
    LIMIT 1
)
-- Step 3: Compare each warehouse to the top one
SELECT 
    wt.warehouse_name,
    wt.total_deliveries,
    tw.max_deliveries AS top_warehouse_deliveries,
    ROUND(
        (wt.total_deliveries - tw.max_deliveries) 
        / tw.max_deliveries * 100, 
        2
    ) AS pct_diff_from_top
FROM warehouse_totals wt
CROSS JOIN top_warehouse tw
ORDER BY wt.total_deliveries DESC;
