What do empty shelves cost?

HardWalmartdate arithmeticcross joinestimatesrank per group· 160 XP (40 per step)

Mock interview. You're interviewing for a senior supply chain analyst job at a large retailer. The interviewer explains: "When a product runs out in a store, we lose sales we'll never see in the data. We want an estimate of what stockouts cost us in the first half of 2026."

Each row in stockouts is one product out of stock in one store, from out_on up to (not including) back_on. So the days out are back_on - out_on.
No sales are recorded while a product is out, so you'll have to estimate what it would have sold.
The first half of 2026 has 181 days.

Step 1: How much stock-out time?

"Start simple: for each department, how many stockouts, how many store-days out of stock in total, and the average length of a stockout?"

Expected output

Columns department, stockouts, days_out, avg_days (rounded to 1 decimal); sorted by days_out, highest first

Example of the shape (made-up values):

department	stockouts	days_out	avg_days
Garden	30	200	6.7

Hints, the later steps and the answer come with Pro.

Step 1 of 4

Write your query here. Ctrl + Enter submits.