How to calculate fill rate in SQL

What fill rate measures and why ops cares

A customer at a B2B grocery distributor places an order for 12 SKUs. The warehouse can ship 10 in full, ships 1 partially, and is out of stock on the last one. The order is partially filled, and a downstream team has to decide whether to back-order, substitute, or refund the missing units. Fill rate quantifies this gap between what was ordered and what was delivered, and it is one of the first KPIs an Amazon, DoorDash, or Stripe-style operations analyst is asked to wire up in their first month.

The reason fill rate matters is that it sits at the intersection of demand forecasting, inventory planning, and supplier reliability — three systems that almost never agree with each other. A 92% unit fill rate looks fine in a dashboard, but if your top 50 SKUs (which generate 70% of revenue) are running at 80%, you are losing money to competitors who happen to be in stock. Enterprise retailers like Walmart and Costco enforce supplier scorecards with vendor compliance penalties when fill rate dips below 95-98%, so the metric has direct cash impact.

Where this becomes an interview question is when a hiring manager asks "calculate weekly fill rate by category, but exclude cancelled orders and treat customer-accepted substitutions as filled." That sentence packs in four edge cases (week boundaries, category grain, cancellations, substitutions) and most candidates collapse at least one. The rest of this post is a default SQL template that handles the cases you will be tested on and hit in production.

The SQL formulas

Three closely related ratios get reported under the "fill rate" umbrella, and senior interviewers love to ask candidates which one they would use and why. The plain definitions are:

Order Fill Rate = orders_fulfilled_complete / total_orders * 100%
Unit Fill Rate  = units_delivered / units_ordered * 100%
Line Fill Rate  = order_lines_delivered_complete / total_order_lines * 100%

Assume the data lives in order_lines(order_id, order_date, sku, category, qty_ordered, qty_delivered, status). A baseline weekly report that computes both line and unit fill rate looks like this:

SELECT
    DATE_TRUNC('week', order_date)                                   AS week,
    COUNT(*)                                                         AS total_lines,
    COUNT(*) FILTER (WHERE qty_delivered >= qty_ordered)             AS lines_complete,
    SUM(qty_ordered)                                                 AS total_units,
    SUM(qty_delivered)                                               AS units_delivered,
    SUM(qty_delivered)::NUMERIC * 100
        / NULLIF(SUM(qty_ordered), 0)                                AS unit_fill_rate_pct,
    COUNT(*) FILTER (WHERE qty_delivered >= qty_ordered)::NUMERIC * 100
        / NULLIF(COUNT(*), 0)                                        AS line_fill_rate_pct
FROM order_lines
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
  AND status NOT IN ('cancelled', 'fraud_hold')
GROUP BY 1
ORDER BY 1;

Two implementation choices deserve a callout. The FILTER (WHERE …) clause is the cleanest way to write conditional aggregates in PostgreSQL, Snowflake, and BigQuery (COUNTIF in standard BigQuery); avoid the older SUM(CASE WHEN …) pattern unless your warehouse forces you to. The NULLIF(…, 0) guard prevents a division-by-zero crash on weeks with no qualifying orders, which happens more often than you would think after migrations.

The order fill rate variant is more common in B2B distribution, where a single late line cascades into a customer-perceived stockout for the whole purchase order:

SELECT
    DATE_TRUNC('week', order_date) AS week,
    COUNT(DISTINCT order_id)        AS total_orders,
    COUNT(DISTINCT order_id) FILTER (
        WHERE order_id NOT IN (
            SELECT order_id
            FROM order_lines
            WHERE qty_delivered < qty_ordered
        )
    )                               AS orders_complete
FROM order_lines
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1;

In production, replace the correlated NOT IN subquery with a window-function pattern (we cover that in SQL window functions interview questions) — it scales better on tables larger than a few hundred million rows.

Line vs unit fill rate

The textbook difference is one line: line fill rate counts complete order lines, unit fill rate counts delivered units. The reason interviewers love testing this is that the two numbers can diverge wildly on the same dataset, and a candidate who cannot explain the divergence has not really thought about the metric.

Picture a single order with 5 lines of 100 units each. Four lines ship complete; one line ships 50 of 100. Line fill rate for that order is 4/5 = 80%. Unit fill rate is (4 × 100 + 50) / (5 × 100) = 450/500 = 90%. The unit number is more forgiving because it gives partial credit on the partially filled line, while line fill rate is the stricter customer-facing view: "did you ship me everything I asked for on this line, yes or no?"

-- Same dataset, two different filled rates
SELECT
    SUM(qty_delivered)::NUMERIC * 100
        / NULLIF(SUM(qty_ordered), 0) AS unit_fill_rate_pct,
    AVG((qty_delivered >= qty_ordered)::INT) * 100 AS line_fill_rate_pct
FROM order_lines
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

A rule of thumb: report line fill rate to the merchandising and customer-success teams, because they care about the customer's binary "did my order ship in full?" experience. Report unit fill rate to the supply-chain and procurement teams, because it tells them how much actual product moved against the demand signal. Companies like Snowflake-data-backed marketplaces and Uber's grocery vertical typically dashboard both side by side so neither team can blame the metric they are not optimizing.

Stock-out rate and worst SKUs

Fill rate aggregates hide a lot. Once you have the weekly chart on a dashboard, the next question from any director-level reviewer is "okay, which SKUs are the worst offenders?" The SKU-level stockout report is the immediate follow-up query:

SELECT
    sku,
    category,
    COUNT(*)                                              AS total_lines,
    COUNT(*) FILTER (WHERE qty_delivered < qty_ordered)   AS partial_or_zero_lines,
    COUNT(*) FILTER (WHERE qty_delivered = 0)             AS hard_stockouts,
    COUNT(*) FILTER (WHERE qty_delivered = 0)::NUMERIC * 100
        / NULLIF(COUNT(*), 0)                             AS stockout_rate_pct
FROM order_lines
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND status NOT IN ('cancelled', 'fraud_hold')
GROUP BY sku, category
HAVING COUNT(*) FILTER (WHERE qty_delivered < qty_ordered) > 0
ORDER BY hard_stockouts DESC, stockout_rate_pct DESC
LIMIT 50;

The two FILTER counts are deliberately separated. A line with qty_delivered > 0 AND qty_delivered < qty_ordered is a partial fill — bad, but the customer received something. A line with qty_delivered = 0 is a hard stockout — the worst possible failure mode short of cancellation. Treating them as one bucket is a common rookie error that hides where the real damage is concentrated. Pair this output with the previous 30-day demand volume per SKU and you have a re-order priority list ready for the inventory planning meeting.

Common pitfalls

The first place fill rate calculations go wrong is with cancelled orders. Treating a cancellation as a fill rate failure punishes operations for an event they had no control over — the customer changed their mind, payment failed, fraud was detected. The fix is to exclude cancellations from the denominator entirely, but you also need to distinguish operational cancellations (warehouse refused the order because of stockout) from customer-initiated ones. Operational cancellations should usually count against fill rate, because the only reason the order died was that you could not ship it.

The second pitfall is back-orders. An order placed on Monday and partially shipped Friday, with the rest delivered the next month, is reported in your weekly Monday cohort or in next month's? Most teams resolve this by reporting fill rate at the original order date but using the final-delivery snapshot of qty_delivered. This means your most recent week is always provisional and gets revised for 2 to 4 weeks until back-orders close out. Skipping this nuance is a fast way to argue with the COO when their dashboard "moved" between Tuesday and Thursday.

The third trap is substitutions. A customer ordered brand A, the warehouse was out, the warehouse shipped brand B with customer consent. Is that line filled? The defensible answer is "filled, with a substitution flag." Build the join logic so substitutions count toward fill rate but get reported separately on a sub-rate dashboard, because if your fill rate is propped up by 30% substitutions you have an inventory problem masquerading as a fulfillment success.

The fourth pitfall is window choice. Fill rate over a week, a month, and a quarter give three different numbers, and small denominators on quiet days create dramatic-looking spikes that are statistical noise. Default to rolling 28-day fill rate for executive reporting and reserve weekly granularity for operational teams who can interpret the noise. If you need to compare year-over-year, normalize for promotional weeks where demand more than doubles — those weeks structurally have lower fill rate because demand outran inventory.

The fifth pitfall is split shipments. An order delivered in two shipments where the total qty_delivered ≥ qty_ordered is fully filled, but a naive query that joins fill data to the first shipment will mark it partial. The fix is to roll up to the order_line grain after aggregating shipment data, never before. Always test this with a manual example like the 5-line, 100-unit case above before signing off on the dashboard.

Optimization tips

For tables in the billions of rows — typical at Walmart, Costco, or a top-50 Shopify merchant on Databricks — the weekly aggregation gets expensive if you re-scan 90 days every run. Partition the source table by order_date and run an incremental materialized view that only refreshes the most recent two weeks plus any open back-orders. Snowflake and BigQuery MATERIALIZED VIEW both handle this cleanly; Databricks users typically use a Delta Live Tables pipeline keyed on order_date.

The other big win is pre-computing line completion as a flag column in your staging table: is_complete = qty_delivered >= qty_ordered. Once that boolean exists, every downstream query is SUM(is_complete) / COUNT(*) instead of FILTER with a comparison, which lets the optimizer push the aggregate further down and often cuts query cost by a third on large fact tables.

Watch out for late-arriving facts. If qty_delivered gets updated days after the order row is inserted, your fill rate of yesterday's orders will keep changing. Build a delivered_at timestamp into the schema and a snapshot table that captures fill rate as_of each reporting date — this way you can answer "what did the dashboard say on the morning of May 1?" without scrolling git history of dbt models.

Related reading

• SQL window functions interview questions
• How to calculate cart abandonment in SQL
• How to calculate days between orders in SQL
• How to calculate AOV in SQL

If you want to drill metrics like this against the edge cases a senior interviewer actually asks, NAILDD ships with hundreds of SQL problems from real e-commerce and logistics interviews.

FAQ

What fill rate is considered good?

For consumer e-commerce, anything above 95% unit fill rate is the working baseline, and category leaders typically run 97-98%. B2B distribution targets 98%+ because a single missing line can block a downstream production run. Pharmaceutical and healthcare distribution often runs to 99%+ because the cost of a stockout is measured in patient harm rather than a refund. The exact target should always be calibrated to your category — luxury goods can tolerate a lower number because customers wait, while staples like diapers and groceries cannot.

Line versus unit fill rate — which one do I report?

Report both, but lead with the one that matches the audience. Customer-success and merchandising want line fill rate because it captures the binary customer experience of "did my order ship in full?" Supply-chain and procurement want unit fill rate because it shows how much demand-weighted product actually moved. If you are forced to pick one for an exec summary, line fill rate is usually more defensible because it ties directly to customer complaints.

My fill rate keeps dropping — what should I investigate first?

Decompose the drop along three axes. First, check whether it is concentrated in a small set of SKUs (a supplier or forecasting problem) or spread across the catalog (a systemic warehouse or labor problem). Second, look at order volume — fill rate often drops during demand spikes you did not plan for, in which case the metric is flagging a stale demand model. Third, separate hard stockouts from partial fills; hard stockouts point to inventory, partial fills point to picking efficiency.

How do substitutions affect fill rate?

A customer-accepted substitution counts as filled in most reasonable definitions, but should always be reported alongside a separate substitution-rate metric. If you bury substitutions inside the fill-rate number, you lose the signal that you have a brand-level stockout problem propped up by customers being flexible. Build the dashboard with two stacked bars: filled-as-ordered and filled-by-substitution, and watch the second one carefully.

What is the difference between fill rate and perfect order rate?

Fill rate measures only the completeness of delivered quantities. Perfect order rate is a composite metric: on-time, complete, damage-free, and accurately invoiced. Fill rate is one of the four inputs into perfect order rate. The composite is a stricter customer experience metric and is often used in vendor scorecards by major retailers, while fill rate is more useful as a day-to-day operational dial because each input metric in perfect order rate has different owners.

Should I report fill rate weighted by revenue?

Yes, especially if your catalog has long-tail SKUs that get few orders. A simple unit fill rate treats a $1 candy bar and a $400 appliance as equivalent demand, which under-weights the SKUs that actually drive your P&L. Compute a revenue-weighted variant — SUM(qty_delivered * unit_price) / SUM(qty_ordered * unit_price) — and put it next to the unweighted version. Executives almost always prefer the weighted view, while operations prefers the unweighted one because it ties directly to picks and labor.