Pandas interview question: find the bugs in outlier capping

MediumCode review

Problem

The colleague’s capped AOV equals the plain AOV in every city, to the cent. Per city, find the September 2026 order count, AOV, and AOV with amounts above the 99th percentile capped at it.

Formulas

cap = quantile(0.99) of amount_usd over all September orders — one cap for all cities
avg_check_capped_usd = mean of min(amount_usd, cap)

Code to fix

Fix it free

# September AOV by city: as is and with outliers capped at the 99th percentile
cap = orders['amount_usd'].quantile(0.99)
sept = orders[(orders['status'] == 'delivered')
              & (orders['created_at'] >= '2026-09-01')
              & (orders['created_at'] < '2026-10-01')].copy()
sept['capped_usd'] = sept['amount_usd']
sept[sept['capped_usd'] > cap]['capped_usd'] = cap
result = sept.groupby('city').agg(orders=('order_id', 'count'), avg_check_usd=('amount_usd', 'mean'), avg_check_capped_usd=('capped_usd', 'mean')).reset_index()

Example

Input: orders

order_id	created_at	status	city	amount_usd	Row note
1	2026-08-20 13:00	delivered	New York	400	← August — not in the cap either
2	2026-09-03 10:00	delivered	New York	32
3	2026-09-04 19:00	delivered	New York	82
4	2026-09-05 12:00	delivered	Boston	20
5	2026-09-06 15:00	delivered	Boston	24
6	2026-09-07 18:00	delivered	Boston	28

Order 1: August — not in the cap either

Expected output

city	orders	avg_check_usd	avg_check_capped_usd
New York	2	57	56
Boston	3	24	24

The cap is the 99th percentile of the five September amounts, $80, so the $82 order counts as $80. New York’s capped AOV is ($32 + $80) / 2 = $56.

Fix it free

Fix the notebook — code checks it on real pandas in your browser. No sign-up needed.

Skills it tests

Reading pandas code
Percentiles
Assignment behavior

Pandas interview question: find the bugs in outlier capping

Problem

Code to fix

Example

Skills it tests

More interview questions