How to calculate Stockout Rate

Formula

Stockout rate = days a product had zero stock ÷ days it should have been available × 100

SQL query

This example assumes: daily_stock(product_id, day, on_hand). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT product_id,
       COUNT(*) FILTER (WHERE on_hand <= 0) * 100.0 / NULLIF(COUNT(*), 0) AS stockout_pct
FROM daily_stock
WHERE day >= CURRENT_DATE - 90
GROUP BY product_id
ORDER BY stockout_pct DESC;

Common mistakes that make Stockout Rate wrong

• Discontinued products with no stock are not stockouts; exclude them.
• Zero stock on a day with no demand matters less than on a peak day; weight by sales if you can.
• Stock recorded at end of day misses intra-day stockouts.

Ask for Stockout Rate in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• Which products were out of stock most often in the last 90 days?
• What revenue might we have lost to stockouts on top sellers?