Purchase Order Analysis — Order Cycle and Supplier Spend

Purchase order analysis tracks procurement spending, order cycle times, cancellation rates, and identifies which supplier-SKU combinations drive the most cost.

1. PO Status Summary

SELECT
    status,
    COUNT(*)                               AS orders,
    SUM(quantity * unit_cost)              AS total_value_usd,
    ROUND((100.0 * COUNT(*) / SUM(COUNT(*)) OVER ())::NUMERIC, 1) AS pct_of_orders
FROM lg_purchase_orders
GROUP BY status
ORDER BY total_value_usd DESC;

What This Returns

status     | orders | total_value_usd | pct_of_orders
-----------+--------+-----------------+--------------
received   |    100 |        658000   |          25.0
in_transit |    100 |        645000   |          25.0
pending    |    100 |        632000   |          25.0
cancelled  |    100 |        624000   |          25.0

2. Order Cycle Time (Order to Receipt)

SELECT
    sup.name                         AS supplier,
    COUNT(po.id) FILTER (WHERE po.received_at IS NOT NULL) AS fulfilled_orders,
    ROUND((AVG(
        DATE_PART('day', po.received_at - po.ordered_at)
    ))::NUMERIC, 1)                            AS avg_cycle_days,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY DATE_PART('day', po.received_at - po.ordered_at)
    )::NUMERIC, 1)                   AS median_cycle_days,
    ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (
        ORDER BY DATE_PART('day', po.received_at - po.ordered_at)
    )::NUMERIC, 1)                   AS p90_cycle_days
FROM lg_purchase_orders po
JOIN lg_suppliers sup ON sup.id = po.supplier_id
WHERE po.received_at IS NOT NULL
GROUP BY sup.name
ORDER BY avg_cycle_days;

3. Top 10 SKUs by Total Purchase Spend

SELECT
    s.sku,
    s.name,
    s.category,
    COUNT(po.id)                         AS orders,
    SUM(po.quantity)                     AS units_ordered,
    SUM(po.quantity * po.unit_cost)      AS total_spend_usd,
    ROUND((AVG(po.unit_cost))::NUMERIC, 2)          AS avg_unit_cost_usd
FROM lg_purchase_orders po
JOIN lg_skus s ON s.id = po.sku_id
GROUP BY s.sku, s.name, s.category
ORDER BY total_spend_usd DESC
LIMIT 10;

4. Monthly Purchasing Spend Trend

SELECT
    DATE_TRUNC('month', ordered_at)::DATE AS month,
    COUNT(*)                               AS orders,
    SUM(quantity * unit_cost)              AS total_spend_usd,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancellations
FROM lg_purchase_orders
GROUP BY 1
ORDER BY 1;

5. Supplier Spend Concentration (Pareto)

Identify whether 20% of suppliers account for 80% of spend:

WITH supplier_spend AS (
    SELECT
        sup.name,
        SUM(po.quantity * po.unit_cost) AS spend
    FROM lg_purchase_orders po
    JOIN lg_suppliers sup ON sup.id = po.supplier_id
    GROUP BY sup.name
),
ranked AS (
    SELECT
        name,
        spend,
        SUM(spend) OVER (ORDER BY spend DESC) AS cumulative_spend,
        SUM(spend) OVER ()                    AS total_spend,
        ROW_NUMBER() OVER (ORDER BY spend DESC) AS rank
    FROM supplier_spend
)
SELECT
    name,
    ROUND((spend)::NUMERIC, 2)             AS spend_usd,
    ROUND((100.0 * spend / total_spend)::NUMERIC, 1) AS pct_of_total,
    ROUND((100.0 * cumulative_spend / total_spend)::NUMERIC, 1) AS cumulative_pct
FROM ranked
ORDER BY rank;

Key Takeaway

SUM(spend) OVER (ORDER BY spend DESC) is the standard running-total technique for Pareto analysis. No GROUP BY is needed — the window function computes the cumulative sum within the result set. When cumulative_pct first exceeds 80, you have found the top suppliers that account for most of your spend — the suppliers that warrant the most negotiation attention and relationship management.