SQL for AML Review: Customers With Two or More Suspicious Receipt Numbers
Company: Stripe
Role: Product Manager
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Other
Visa's anti-money-laundering (AML) team flags potentially suspicious transactions so the company stays compliant with financial regulations worldwide. Part of each investigation follows the receipts attached to large transactions, and every receipt carries a receipt number.
The team treats three patterns in a receipt number as suspicious: `999`, `1234` and `XYZ`. A customer with at least two receipts matching these patterns is flagged as a possible money launderer.
Write a SQL query that lists every suspicious receipt belonging to a flagged customer. Each row shows the customer's first and last name, the suspicious receipt number, and how many offences that customer has in total.
**customers**
| Column | Type | Meaning |
|---|---|---|
| customer_id | integer | Unique customer identifier |
| first_name | string | Customer's first name |
| last_name | string | Customer's last name |
**transactions**
| Column | Type | Meaning |
|---|---|---|
| transaction_id | integer | Unique transaction identifier |
| customer_id | integer | Customer who made the transaction |
| receipt_number | string | Receipt number of the transaction |
| amount | float | Amount of money involved |
Return these columns: `first_name`, `last_name`, `receipt_number`, `no_of_offences`.
```hint Two levels of detail
Whether a customer is flagged is a per-customer fact, but the output has one row per receipt. Work out how to compute the per-customer count while still keeping the individual receipt rows.
```
### Clarifying Questions
- Does "matching a pattern" mean the receipt number contains the pattern anywhere, starts with it, or equals it? Is `XYZ` matched case-sensitively?
- If the same receipt number appears on two transactions for one customer, is that one offence or two?
- Does the focus on large transactions imply an amount threshold, or does every transaction with a matching receipt count?
- In what order should the rows be returned?
### What a Strong Answer Covers
- A precise, stated definition of a pattern match, applied so that a receipt matching several patterns counts only once
- Offences counted per `customer_id` rather than per name, with only customers at two or more kept
- Only the suspicious receipts of flagged customers returned, each carrying the customer's total count
- Handling of NULL receipt numbers, duplicate receipts and customers missing from `customers`, plus a deterministic ORDER BY
### Follow-up Questions
- The pattern list will change often and may grow large. How would you restructure the query so the patterns live in a table?
- Substring matching cannot use an ordinary index. How would this query perform on a very large transactions table, and what would you change?
- As the product manager for this tool, how would you find out whether these patterns catch real laundering rather than flagging innocent customers?
Overview: A SQL exercise set in an anti-money-laundering review: flag customers who have two or more receipt numbers matching suspicious patterns and list each of those receipts with the customer's offence count. It tests substring matching, per-customer counting that keeps receipt-level rows, and careful handling of duplicates and shared names.