Fill rate, OTIF, rolling demand, ABC classification, MAPE, bias, dead stock and stockout risk — copy-ready SQL, plus the four mistakes that silently give you wrong numbers.
Most supply chain analysts can write a SELECT with a JOIN and stop there. That is enough to pull a report and nowhere near enough to answer the questions planners actually ask — which are almost always about change over time, ranking within groups, and gaps between what was promised and what happened.
Those three question shapes map to a small set of SQL patterns. Learn these twelve and you can answer perhaps 80% of the analytical questions a planning team will bring you, without exporting anything to Excel.
All examples use standard SQL (PostgreSQL flavour). Assume three tables: orders (order_id, sku, region, order_date, qty_ordered, qty_delivered, promised_date, delivered_date), inventory (sku, location, snapshot_date, qty_on_hand, unit_cost), and forecast (sku, region, period, forecast_qty, actual_qty).
The most-requested number in any supply chain. Note the NULLIF — without it, a region with zero orders divides by zero and kills your whole query.
SELECT region,
SUM(qty_delivered)::numeric / NULLIF(SUM(qty_ordered),0) AS fill_rate
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY region
ORDER BY fill_rate ASC;
Two conditions, one metric. The trap is treating them separately — an order that is on time but short is not OTIF.
SELECT region,
AVG(CASE WHEN delivered_date <= promised_date
AND qty_delivered >= qty_ordered THEN 1 ELSE 0 END) AS otif
FROM orders
WHERE delivered_date IS NOT NULL
GROUP BY region;
Counting late orders is nearly useless. Ranking them by the money involved is what gets a meeting moved.
SELECT o.sku, o.region,
delivered_date - promised_date AS days_late,
o.qty_ordered * i.unit_cost AS value_at_risk
FROM orders o
JOIN inventory i ON i.sku = o.sku
WHERE delivered_date > promised_date
ORDER BY value_at_risk DESC
LIMIT 20;
If you take one thing from this issue, take this section. Window functions are the difference between "I can pull data" and "I can answer questions".
LAG() looks at the previous row in a defined order. This single pattern answers half of all "is it going up or down" questions.
SELECT sku, period, actual_qty,
LAG(actual_qty) OVER (PARTITION BY sku ORDER BY period) AS prev_qty,
actual_qty - LAG(actual_qty) OVER (PARTITION BY sku ORDER BY period) AS mom_change
FROM forecast
ORDER BY sku, period;
The smoothing planners actually want, computed in the database rather than in a spreadsheet nobody can audit.
SELECT sku, period, actual_qty,
AVG(actual_qty) OVER (
PARTITION BY sku ORDER BY period
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3m_avg
FROM forecast;
ROW_NUMBER() with a subquery filter. This "top N within each group" pattern comes up constantly and cannot be done cleanly with GROUP BY alone.
SELECT * FROM (
SELECT sku, region, SUM(qty_ordered) AS total_qty,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(qty_ordered) DESC) AS rn
FROM orders GROUP BY sku, region
) t WHERE rn <= 3;
Pareto analysis in pure SQL. This replaces an entire manual Excel ritual in most companies.
WITH val AS (
SELECT sku, SUM(qty_ordered * unit_cost) AS annual_value
FROM orders o JOIN inventory i USING (sku) GROUP BY sku
), cum AS (
SELECT sku, annual_value,
SUM(annual_value) OVER (ORDER BY annual_value DESC)
/ SUM(annual_value) OVER () AS cum_pct
FROM val
)
SELECT sku, annual_value,
CASE WHEN cum_pct <= 0.8 THEN 'A'
WHEN cum_pct <= 0.95 THEN 'B' ELSE 'C' END AS abc_class
FROM cum;
Guard against divide-by-zero on periods with no actual demand — a mistake that silently produces infinite MAPE and wrecks the average.
SELECT sku,
AVG(ABS(forecast_qty - actual_qty)::numeric / NULLIF(actual_qty,0)) AS mape
FROM forecast
WHERE actual_qty > 0
GROUP BY sku
ORDER BY mape DESC;
MAPE tells you how wrong you were. Bias tells you whether you are consistently over or under — which is the fixable kind of wrong.
SELECT sku,
SUM(forecast_qty - actual_qty)::numeric / NULLIF(SUM(actual_qty),0) AS bias
FROM forecast
GROUP BY sku
HAVING ABS(SUM(forecast_qty - actual_qty)::numeric / NULLIF(SUM(actual_qty),0)) > 0.1;
An anti-join. Stock that exists in inventory but has no order history in the window — usually the fastest cash you will ever find.
SELECT i.sku, i.location, i.qty_on_hand,
i.qty_on_hand * i.unit_cost AS tied_up_cash
FROM inventory i
LEFT JOIN orders o
ON o.sku = i.sku AND o.order_date >= CURRENT_DATE - INTERVAL '180 days'
WHERE o.sku IS NULL AND i.qty_on_hand > 0
ORDER BY tied_up_cash DESC;
Stock on hand divided by recent daily demand. The number that tells a planner whether to panic.
WITH daily AS (
SELECT sku, SUM(qty_ordered)::numeric / 90 AS avg_daily
FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY sku
)
SELECT i.sku, i.qty_on_hand, d.avg_daily,
i.qty_on_hand / NULLIF(d.avg_daily,0) AS days_of_cover
FROM inventory i JOIN daily d USING (sku)
ORDER BY days_of_cover ASC;
The query that earns its keep. Anything where days of cover is less than replenishment lead time is a stockout waiting to happen.
WITH cover AS (
/* days_of_cover query above as a CTE */
)
SELECT sku, days_of_cover, lead_time_days,
days_of_cover - lead_time_days AS buffer_days
FROM cover JOIN sku_master USING (sku)
WHERE days_of_cover < lead_time_days
ORDER BY buffer_days ASC;
In PostgreSQL, 5/2 is 2, not 2.5. Cast to numeric or every ratio you compute is silently wrong.
Always NULLIF(denominator,0). A single zero-demand SKU can blow up an entire accuracy report.
Joining orders to a table with multiple matching rows silently multiplies your quantities. Check row counts before and after every join.
Putting a condition on the right table in WHERE turns your LEFT JOIN into an INNER JOIN. Put it in the ON clause instead.
Take any one of these twelve, run it against real data, and then explain the result to a planner. If they ask a follow-up you cannot answer with a modification of the same query, that gap is your next thing to learn. That loop is worth more than any course syllabus.
These queries are the SQL layer of every project worth building. If you want to see how they slot into complete, end-to-end builds — a demand forecasting system, a control tower, an inventory optimiser — the companion issue on building supply chain analytics projects covers five full blueprints with architecture and free data sources.
And if you would rather learn this with feedback than alone, our Supply Chain Analytics program builds exactly these patterns on real data across 18 weeks, alongside Python, Excel and Power BI.
SQL, Python, Excel and Power BI with AI copilots. 18 live weeks, 110+ hours, real supply chain data — and a portfolio you can show in interviews.
Join supply chain leaders getting practical AI, forecasting, inventory and optimisation playbooks — plus new tools, courses and case studies. No spam, unsubscribe anytime.
📰 Latest from Mathnal Insights
Hormuz closure disrupts 20% oil, 34% helium, 46% urea. Brent +55%, freight +50%. Every route, cost & mitigation quantified.
Issue #9 · ESG & Scope 3EU CSRD fines 5% revenue, UK CMA 10% turnover, 150+ US lawsuits. 6 regulations, 8 warning signs, 6-pillar compliance framework.
Free Tool · Interactive SimulatorBayesian risk engine, 45 scenarios, Monte Carlo simulation, VaR/CVaR — simulate the Hormuz crisis on your supply chain.
View all 10 newsletters → | Free diagnostic tools → | CSCOP Certification →