Walmart: Repeat Receiptless Returns Across Stores

Medium
1013.

Walmart: Repeat Receiptless Returns Across Stores

MediumAggregationDates

MySQL

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

ColumnType
return_idint
customer_idint
store_idint
return_datedate
has_receiptbool
refund_amountint

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.

Example

The rows must come back in the order shown.

ReturnTxn

return_idcustomer_idstore_idreturn_datehas_receiptrefund_amount
12112042025-01-04060
22133182025-01-11045
32112042025-02-02080
42212042025-01-05030
52212042025-01-19030
62212042025-02-09030
72312042024-12-28090
82333182025-01-03020
92352602025-01-10025
102352602025-01-121300
4 more rows

Output

customer_idreceiptless_returnsstoresrefunded
2132185
2433185
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
return_idint
customer_idint
store_idint
return_datedate
has_receiptbool
refund_amountint

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_idcustomer_idstore_idreturn_datehas_receiptrefund_amount
12112042025-01-04060
22133182025-01-11045
32112042025-02-02080
42212042025-01-05030
52212042025-01-19030
62212042025-02-09030
72312042024-12-28090
82333182025-01-03020
92352602025-01-10025
102352602025-01-121300
11NULL12042025-01-07070
122433182025-01-08050
132452602025-01-21070
142412042025-03-01065

Output

customer_idreceiptless_returnsstoresrefunded
2132185
2433185

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_id

Another 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 →