Inventory Reorder Analysis — Identifying Stock Below Reorder Point

Reorder analysis answers: which SKUs are below their reorder threshold, what quantity needs to be ordered, and what is the total replenishment cost?

1. Items Below Reorder Point

SELECT
    w.code                          AS warehouse,
    w.city,
    s.sku,
    s.name,
    s.category,
    i.quantity                      AS current_stock,
    i.reorder_point,
    i.reorder_qty,
    i.reorder_point - i.quantity    AS shortage,
    ROUND(s.unit_cost * i.reorder_qty, 2) AS replenishment_cost_usd
FROM lg_inventory i
JOIN lg_warehouses w ON w.id = i.warehouse_id
JOIN lg_skus s       ON s.id = i.sku_id
WHERE i.quantity < i.reorder_point
ORDER BY shortage DESC;

What This Returns

warehouse | city          | sku_code      | name     | current_stock | reorder_point | shortage
----------+---------------+----------+----------+---------------+---------------+---------
US-NY     | New York      | SKU-002  | Widget B |             1 |            51 |       50
US-LA     | Los Angeles   | SKU-011  | Board A  |             0 |            68 |       68
...

2. Reorder Summary by Warehouse

SELECT
    w.code,
    w.city,
    COUNT(*) FILTER (WHERE i.quantity < i.reorder_point)  AS skus_below_reorder,
    COUNT(*) FILTER (WHERE i.quantity = 0)                AS out_of_stock,
    SUM(CASE WHEN i.quantity < i.reorder_point
             THEN ROUND(s.unit_cost * i.reorder_qty, 2) ELSE 0 END) AS total_replenishment_cost_usd
FROM lg_inventory i
JOIN lg_warehouses w ON w.id = i.warehouse_id
JOIN lg_skus s       ON s.id = i.sku_id
GROUP BY w.code, w.city
ORDER BY skus_below_reorder DESC;

3. Days of Stock Remaining (Estimated)

Using recent purchase order velocity as a proxy for consumption rate:

WITH consumption AS (
    SELECT
        warehouse_id,
        sku_id,
        SUM(quantity)::NUMERIC /
            NULLIF(COUNT(DISTINCT DATE_TRUNC('month', ordered_at)), 0) AS avg_monthly_demand
    FROM lg_purchase_orders
    WHERE received_at IS NOT NULL
    GROUP BY warehouse_id, sku_id
)
SELECT
    w.code,
    s.sku,
    s.name,
    i.quantity AS current_stock,
    ROUND(c.avg_monthly_demand, 0) AS avg_monthly_demand,
    ROUND(i.quantity / NULLIF(c.avg_monthly_demand / 30, 0), 0) AS days_of_stock_remaining
FROM lg_inventory i
JOIN lg_warehouses w ON w.id = i.warehouse_id
JOIN lg_skus s       ON s.id = i.sku_id
LEFT JOIN consumption c
    ON c.warehouse_id = i.warehouse_id AND c.sku_id = i.sku_id
ORDER BY days_of_stock_remaining NULLS LAST;

4. Open Purchase Orders That Will Cover the Shortage

Match in-transit POs against below-reorder inventory:

SELECT
    w.code                              AS warehouse,
    s.sku,
    s.name,
    i.quantity                          AS current_stock,
    i.reorder_point,
    po.quantity                            AS inbound_quantity,
    po.expected_at,
    po.status                           AS po_status,
    i.quantity + po.quantity               AS projected_stock_after_receipt
FROM lg_inventory i
JOIN lg_warehouses w ON w.id = i.warehouse_id
JOIN lg_skus s       ON s.id = i.sku_id
JOIN lg_purchase_orders po
    ON po.warehouse_id = i.warehouse_id
    AND po.sku_id = i.sku_id
    AND po.status IN ('pending','in_transit')
WHERE i.quantity < i.reorder_point
ORDER BY w.code, s.sku;

Key Takeaway

WHERE quantity < reorder_point with a LEFT JOIN to open POs lets you produce an actionable reorder report in a single query. Adding days_of_stock_remaining using a velocity calculation converts a static snapshot into a forward-looking risk indicator — a SKU with 0 days remaining requires different urgency than one with 5 days.