Inventory Analysis

Hook: Inventory analysis is the warehouse version of checking your bank balance before you spend it — interviewers love it because one small SQL mistake can turn into lost sales, angry customers, or stale cash.

Question: How do you analyze inventory in SQL to find low stock, overstock, stale items, and bad data?

Answer: Start from the inventory movement table, not just the current snapshot. Sum receipts, sales, and adjustments to get on-hand quantity, then compare that number with reorder point, safety stock, and recent demand to spot items that need attention. Add date logic for last movement and days of supply so you can separate fast movers from dead stock.

Interview-Ready Answer: I’d treat inventory analysis as a reconciliation problem first. I start from the transaction ledger, aggregate each product to get current on-hand, then calculate metrics like last movement date, 30-day sales, and days of supply. After that I classify items into healthy, low stock, dead stock, or data issues like negative inventory, because that catches both business risk and source-data problems.

What inventory analysis really means

Detailed Explanation: In SQL, inventory analysis usually means answering business questions from two kinds of data: the ledger (a transaction log of every stock change) and the snapshot (the current quantity on hand). The safest mental model is: rebuild reality from movements, then judge health from the result. That way you catch missing receipts, duplicate shipments, returns, adjustments, and negative stock before the dashboard lies to you.

How it works under the hood

1. Start with one row per movement. Each row should represent one stock event, such as receiving 100 units, selling 3 units, or correcting inventory by -2 units. Use sign conventions carefully: receipts are usually positive, outbound sales are negative, and returns or corrections may be positive or negative depending on the business rule.
2. Aggregate by product (and warehouse if needed). A GROUP BY over the movement table gives current on-hand quantity. If you have multiple warehouses, include warehouse_id in the grouping key or you will accidentally mix locations together.
3. Compute demand signals. Count sales in a recent window such as the last 30 days. This gives you a simple demand pace. From there, days of supply means how many days your current stock should last at the recent sales rate.
4. Compare against business thresholds. Safety stock is the buffer you do not want to cross; reorder point is the level that says, “place an order now.” Those are business rules, not database rules, so they belong in the data model and in the query.
5. Classify the result. A practical output is a status like HEALTHY, LOW STOCK, DEAD STOCK, OUT OF STOCK, or DATA ISSUE. That lets a planner scan one result set instead of reading raw numbers.
6. Protect against edge cases. Use COALESCE for missing rows, NULLIF to avoid divide-by-zero when sales are zero, and explicit checks for negative inventory. Negative stock usually means a timing issue, bad integration, or a missing movement row.

Snapshot vs movement table

In practice, the ledger is the source of truth, and a snapshot is a performance layer built from it. If you only store snapshots, you lose the why behind the number; if you only store movements, large reports can become slow.

Performance and complexity

For a straight aggregate, the work is roughly O(n) over the number of movement rows the database must read, but real execution can look like O(n log n) if the engine has to sort, spill to disk, or compute window functions. On a small catalog of 50,000 SKUs, this is easy. On 100 million movement rows across many warehouses, you usually want an index on (product_id, txn_date) or (warehouse_id, product_id, txn_date), and sometimes partitioning by month so the engine can prune old data. A nightly snapshot table can also reduce report cost from scanning millions of transactions to scanning tens of thousands of product rows.

Important edge cases

• No sales in the window: days of supply should become NULL or “infinite,” not a divide-by-zero error.
• Returns and corrections: make sure the sign convention is consistent; otherwise you can inflate stock by accident.
• Fan-out joins: joining inventory to orders and order_lines without care can duplicate rows and overcount stock movements.
• Multi-warehouse stock: product totals can hide one warehouse being empty while another is full.
• Time boundaries: define whether “last 30 days” means rolling 30*24 hours or calendar days; interviewers like this detail because it changes results.

Memory in one line: Inventory analysis is a scale in one hand and a calendar in the other. The scale tells you how much you have; the calendar tells you whether it is moving.

Real-World Example: Imagine an ecommerce checkout service during a holiday flash sale. The warehouse dashboard says there are 400 keyboards left, but orders start failing because the shelf is actually empty. The bug was a bad SQL query that summed every movement as a positive number, so sales never subtracted from stock. Customers saw “out of stock after payment,” support got flooded with cancellations, and the logs showed mismatched inventory totals after the nightly sync. The fix was to rebuild inventory from signed movements, add a negative-stock alert, and separate returns from receipts so the dashboard could not lie again.

Follow-up & Tricky Questions:

• How would you analyze inventory by warehouse? Add warehouse_id to the grouping key and to every filter. Otherwise one full warehouse can hide an empty one.
• How do you find dead stock? Look for items with no sales over a long window, such as 60 or 90 days, and still-positive on-hand quantity. Pair that with age since last movement to avoid flagging active but slow items incorrectly.
• How would you calculate days of supply? Divide current on-hand by average daily sales over a chosen window. Use NULLIF so products with zero sales do not crash the query.
• How do returns change the query? Returns are movements too, but they should not be counted as sales. Keep the sign convention explicit, or your stock and demand metrics will drift apart.
• How do you make this fast on millions of rows? Keep a ledger table as the source of truth, then build a daily summary or materialized view for reporting. Index the join keys and prune old partitions so the engine reads less data.
• Tricky: Why is SUM(quantity) sometimes wrong? Because if receipts and sales are both stored as positive numbers, the sum does not represent stock. You need a signed convention or separate in/out columns before aggregation makes sense.
• Tricky: What if a product has no movement rows at all? A left join should keep the product, and you should classify it as out of stock or unknown rather than dropping it from the report. Missing rows are often the most important signal in inventory.
• Tricky: Is low stock the same as reorder soon? Not exactly. Low stock means you are near the safety buffer; reorder soon means you have reached the business trigger to place an order. Many candidates collapse them, but interviewers like the distinction.

Common Mistakes:

• Using only the current snapshot. Correction: rebuild stock from movements when you need auditability, then optionally cache a snapshot for speed.
• Forgetting sign conventions. Correction: decide once whether outbound stock is negative or stored in a separate column, and keep it consistent everywhere.
• Dividing by zero. Correction: wrap the denominator with NULLIF and return NULL or a clear label when there are no sales.
• Mixing warehouses or categories accidentally. Correction: group on the full business key, not just product_id.

Memory Hook: Scale + Calendar. The scale tells you how much stock exists; the calendar tells you whether it is moving. If one is missing, the answer is incomplete.

Cheat Sheet:

• Inventory analysis starts with the movement ledger.
• SUM(qty_signed) gives on-hand stock when the sign convention is correct.
• MAX(txn_date) gives last movement date for aging checks.
• NULLIF protects days-of-supply math from zero sales.
• Use reorder point and safety stock to classify urgency.
• For scale, index (product_id, txn_date) or partition by date.

Practice Tasks:

• Compute inventory by product_id and warehouse_id together.
• Add a 90-day sell-through percentage for each product.
• Flag products whose on-hand stock is negative or whose last movement is older than 120 days.