SQL for Supply Chain: Optimizing Inventory Across Multiple Warehouses with Window Functions

Multi-Warehouse Inventory Optimization

Distributing stock across 3+ warehouses while minimizing stockouts and transfer costs requires smart aggregation and window functions.

1. Total Stock & Imbalance Score per Product

Show total inventory across all warehouses and flag products that are heavily imbalanced (one warehouse has most of the stock).

SELECT
    product_id,
    SUM(warehouse_stock) AS total_stock,
    AVG(warehouse_stock) AS avg_per_warehouse,
    MAX(warehouse_stock) AS max_stock,
    MIN(warehouse_stock) AS min_stock,
    MAX(warehouse_stock) - MIN(warehouse_stock) AS imbalance_range,
    ROUND(100.0 * (MAX(warehouse_stock) - AVG(warehouse_stock)) / AVG(warehouse_stock), 2) AS imbalance_pct
FROM warehouse_inventory
GROUP BY product_id
ORDER BY imbalance_pct DESC;

2. Recommend Transfer to Balance Stock

For imbalanced products, recommend how many units to transfer from the surplus warehouse to the deficit warehouse.

WITH stock_summary AS (
    SELECT
        product_id,
        warehouse_id,
        warehouse_stock,
        AVG(warehouse_stock) OVER (PARTITION BY product_id) AS avg_stock
    FROM warehouse_inventory
),
imbalances AS (
    SELECT
        product_id,
        warehouse_id,
        warehouse_stock,
        warehouse_stock - avg_stock AS diff_from_avg
    FROM stock_summary
    WHERE warehouse_stock > avg_stock  -- Only surplus warehouses
)
SELECT
    product_id,
    warehouse_id,
    warehouse_stock,
    diff_from_avg,
    SUM(diff_from_avg) OVER (PARTITION BY product_id) AS total_surplus
FROM imbalances;

3. Weekly Stock Trend Analysis

Compare this week's total stock against the 4-week rolling average to detect seasonal draws or supply disruptions.

WITH weekly_totals AS (
    SELECT
        product_id,
        DATE_TRUNC('week', measurement_date) AS week,
        SUM(warehouse_stock) AS total_stock
    FROM warehouse_inventory
    GROUP BY product_id, week
),
rolling_avg AS (
    SELECT
        product_id,
        week,
        total_stock,
        AVG(total_stock) OVER (
            PARTITION BY product_id
            ORDER BY week
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        ) AS avg_4week
    FROM weekly_totals
)
SELECT
    product_id,
    week,
    total_stock,
    ROUND(avg_4week, 2) AS avg_4week,
    CASE WHEN total_stock < avg_4week * 0.8 THEN 'LOW STOCK'
         WHEN total_stock > avg_4week * 1.2 THEN 'OVERSTOCK'
         ELSE 'BALANCED' END AS status
FROM rolling_avg
ORDER BY product_id, week DESC;

Key Takeaways for Production

• Window functions with PARTITION BY let you compare each warehouse against the product average — no self-joins needed.
• Imbalance detection (MAX - MIN, MAX / AVG) is the first step before recommending transfers — don't transfer blindly.
• Rolling averages (>3-week) smooth out weekly seasonality and reveal true demand shifts.
• Index product_id and warehouse_id for performance on millions of inventory records.