Hunting down missing items

HardInstacartmulti-table joinsbaselinesbool_andrank· 160 XP (40 per step)

Mock interview. You're interviewing for a senior analyst job on a grocery delivery team. The interviewer explains: "When a shopper can't find an item, they swap it for something else (replaced) or refund it (refunded). Customers hate both. Help us find where it's going wrong."

Each row in order_items is one product in one order, with item_status of found, replaced or refunded. Use delivered orders only (cancelled orders were never shopped).

Step 1: Which departments?

"Start wide. For each product department, show how many items were ordered and the found, replaced and refunded rates."

Expected output

Rates as percentages rounded to 1 decimal; sorted by found_rate, lowest first

Example of the shape (made-up values):

department	items	found_rate	replaced_rate	refunded_rate
deli	900	88.0	7.0	5.0

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

Step 1 of 4

Write your query here. Ctrl + Enter submits.