Mission M28 · SQL · 15 min

Reorder alert: stock below reorder point

Request from your manager

The supply chain manager needs Monday's purchase list: "Which store-product combinations are below their reorder point in the December 31, 2025 snapshot? How many lines will the list have?"

Data

Download the practice file(s):

⬇ sunshine_grocers.xlsx

Steps

  1. Join inventory to stores (store_id) and products (product_id) so the list shows names, not IDs.
  2. Filter WHERE on_hand < reorder_point.
  3. Add a column units_short = reorder_point - on_hand and sort by it DESC so the worst gaps come first.
  4. Run a second query with COUNT(*) to know how many lines the purchase list has.

Check your answer

How many store-product rows are below their reorder point?

Show solution

90 of the 324 store-product rows (12 stores × 27 products) are below the reorder point.

-- Purchase list
SELECT s.store_name, p.product_name, i.on_hand, i.reorder_point,
       i.reorder_point - i.on_hand AS units_short
FROM inventory i
JOIN stores s   ON s.store_id = i.store_id
JOIN products p ON p.product_id = i.product_id
WHERE i.on_hand < i.reorder_point
ORDER BY units_short DESC;

-- How many lines?
SELECT COUNT(*) AS rows_below
FROM inventory
WHERE on_hand < reorder_point;

Answer: 90 rows

← Campaign ROI by marketing channelLoad the sales file with pandas →