Walmart: Repeat Receiptless Returns Across Stores
Walmart's asset protection team looks for return patterns that can signal fraud: the same customer bringing back items without a receipt again and again, at…
- Difficulty: Medium
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #1013
Problem statement
Walmart's asset protection team looks for return patterns that can signal fraud: the same customer bringing back items without a receipt again and again, at more than one store.
Considering only receiptless returns (has_receipt = 0) made in 2025 by an identified customer (returns with a NULL customer_id are ignored), return each customer with at least 3 such returns at at least 2 different stores: customer_id, receiptless_returns, stores (distinct stores among those returns) and refunded (their total refund_amount). Order by refunded descending, then customer_id ascending.
A practice scenario set at Walmart: the tables and data are invented, and this is not a question from, or endorsed by, Walmart.
Tables
Table: ReturnTxn
| Column | Type |
|---|---|
| return_id | int |
| customer_id | int |
| store_id | int |
| return_date | date |
| has_receipt | bool |
| refund_amount | int |
Primary key: return_id.
One row per return at a service desk. customer_id is NULL when the shopper gave no ID; refund_amount is in whole dollars.
Examples
Example 1
ReturnTxn
| return_id | customer_id | store_id | return_date | has_receipt | refund_amount |
|---|---|---|---|---|---|
| 1 | 21 | 1204 | 2025-01-04 | 0 | 60 |
| 2 | 21 | 3318 | 2025-01-11 | 0 | 45 |
| 3 | 21 | 1204 | 2025-02-02 | 0 | 80 |
| 4 | 22 | 1204 | 2025-01-05 | 0 | 30 |
| 5 | 22 | 1204 | 2025-01-19 | 0 | 30 |
| 6 | 22 | 1204 | 2025-02-09 | 0 | 30 |
| 7 | 23 | 1204 | 2024-12-28 | 0 | 90 |
| 8 | 23 | 3318 | 2025-01-03 | 0 | 20 |
| 9 | 23 | 5260 | 2025-01-10 | 0 | 25 |
| 10 | 23 | 5260 | 2025-01-12 | 1 | 300 |
| 11 | NULL | 1204 | 2025-01-07 | 0 | 70 |
| 12 | 24 | 3318 | 2025-01-08 | 0 | 50 |
| 13 | 24 | 5260 | 2025-01-21 | 0 | 70 |
| 14 | 24 | 1204 | 2025-03-01 | 0 | 65 |
Output
| customer_id | receiptless_returns | stores | refunded |
|---|---|---|---|
| 21 | 3 | 2 | 185 |
| 24 | 3 | 3 | 185 |
How to solve Walmart: Repeat Receiptless Returns Across Stores
The pattern is defined on a set of returns, so the first job is to decide which returns are in that set: no receipt, dated in 2025, and tied to a customer. All three are row-level tests and belong in WHERE. A receipted return by the same customer, or one from late December 2024, must not count towards either threshold — filtering first guarantees that.
NULL customers need a decision of their own. GROUP BY puts every NULL customer_id into one group, which would lump anonymous shoppers together into a fake "customer"; excluding them explicitly keeps them out.
Then the two thresholds are aggregates of each customer's group: COUNT(*) >= 3 returns and COUNT(DISTINCT store_id) >= 2 stores, combined with AND in HAVING. A customer who made three receiptless returns at one store is not on the list, however much was refunded. The alternative groups first and applies the thresholds (and the NULL test) in an outer query. One grouped scan either way.
Reference solution (MySQL)
SELECT customer_id,
COUNT(*) AS receiptless_returns,
COUNT(DISTINCT store_id) AS stores,
SUM(refund_amount) AS refunded
FROM ReturnTxn
WHERE has_receipt = 0
AND customer_id IS NOT NULL
AND return_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY customer_id
HAVING COUNT(*) >= 3 AND COUNT(DISTINCT store_id) >= 2
ORDER BY refunded DESC, customer_idAnother way
SELECT customer_id, receiptless_returns, stores, refunded FROM (
SELECT customer_id, COUNT(*) AS receiptless_returns, COUNT(DISTINCT store_id) AS stores, SUM(refund_amount) AS refunded
FROM ReturnTxn
WHERE NOT has_receipt AND YEAR(return_date) = 2025
GROUP BY customer_id
) t
WHERE customer_id IS NOT NULL AND receiptless_returns >= 3 AND stores >= 2
ORDER BY refunded DESC, customer_id ASC← Walmart: Each Store's Sales Gap to Its Region's Leader · Walmart: Item Records Sharing a UPC After Cleanup →