Clean and Reorder: repair the sales feed, then decide what to reorder

Challenge — test yourself

• Combines: Data Engineering × Supply Chain
• Difficulty: Beginner · 4/10
• You bring: Read CSVs and group/aggregate with pandas, Deduplicate rows, keeping the latest by a timestamp, Fill a table out to a full date range so missing rows count as zero, Do arithmetic across two tables joined on a key
• Time: ~1.5–2 h
• You’ll use: pandas
• No guided steps. A working starter that starts wrong, a hidden acceptance test, and your own approach. You do not need to have done a course here first — if the assumed skills above sound doable, dive in.

This is a Challenge, not a tutorial. There’s no guided walkthrough — just a realistic mess, a question, and a test that tells you when you’ve nailed it. You bring the approach. You don’t need to have done any course here first; if the assumed skills below sound doable, dive in.

The situation

It’s Monday. Ops wants the reorder list — which SKUs are at or below their reorder point and must be ordered today, and how many units of each — off last quarter’s sales feed.

You’ve been handed two files for the 90-day window 2026-01-01 → 2026-03-31:

• data/sales_feed.csv — the daily point-of-sale feed: sku, sale_date, units, ingest_ts.
• data/params.csv — per-SKU policy inputs: sku, lead_time_days, safety_stock, on_hand.

The feed is the kind of thing that actually lands in an inbox:

• the POS writes a row only for days a SKU sold something — a zero-sales day has no row at all, so most SKUs are missing a lot of days,
• a sync sometimes re-sends a (sku, sale_date) record: an earlier attempt with a pre-correction quantity plus the final corrected row — same day, later ingest_ts,
• units arrives as text — "12", "1,024", "8.0", " 37 " — not numbers.

The trap is the missing days. If you average each SKU’s demand over the days that appear in the feed, every SKU looks busier than it is — worst for the slow movers — and their reorder points come out too high, so you reorder things you don’t need to.

Your deliverable

The policy is given — apply it exactly:

average_daily_demand = total units sold over the window / 90     # the FULL window, gaps = 0
reorder_point (s)    = average_daily_demand * lead_time_days + safety_stock
order_up_to (S)      = reorder_point + 14 * average_daily_demand  # 14-day review coverage

A SKU must be reordered now iff on_hand <= reorder_point. Its order quantity is round(order_up_to - on_hand).

Write two files into out/:

• reorder_list.csv — one row per SKU to reorder now: sku, on_hand, reorder_point, order_qty.
• report.json — with:
  • clean_days_used — the number of SKU-day cells in your zero-filled demand table (should be number_of_skus * 90),
  • skus_to_reorder — how many SKUs are at or below their reorder point,
  • total_reorder_units — the total units across the whole reorder list.

Run it

python data/make_data.py        # once — generates data/ (synthetic, seeded)
python starter.py               # your working file; it starts naive and wrong
pytest test_solution.py -q

The starter.py already produces the right output shape with the wrong numbers — fix the three # TODOs. You’re done when all three tests pass: your demand is averaged over the full calendar, your reorder set matches (F1 ≥ 0.90 against the true set), and total_reorder_units lands within 2% of the truth. A naive read (average over present days, retries left in) flags extra slow movers and fails.

Why this is a cross-domain challenge

It’s deliberately not solvable from one skill set:

• Data Engineering — repair the feed so each SKU’s true daily demand is trustworthy: the raw feed has quirks (text quantities, repeated records, and days that never appear) that quietly distort demand. Miss any of them and the number you hand the policy is wrong.
• Supply-Chain — apply the reorder-point / order-up-to policy: turn demand + lead time
  • safety stock into a reorder point, decide which SKUs are at or below it, and size each order.

Clean the feed but stop there and you have no reorder list. Run the reorder math on the raw feed and you double-count the retries and divide by too few days — demand is inflated, so you reorder SKUs that are actually fine. Both halves have to be right.

Self-check (before you trust it)

• clean_days_used should be exactly number_of_skus * 90 — if it’s smaller, you never built the full calendar and your slow-mover demand is too high.
• Re-running python data/make_data.py never changes the data (it’s seeded), so your reorder list shouldn’t move between runs.
• Your reorder set should be smaller than what you’d get averaging over present days only — the SKUs that drop out are the slow movers the gap-fill rescues.