Card-testing patterns
hardCard-testing patterns
Mastercard SQL Interview Question
Fraudsters often test a stolen card by making small payments at several different merchants on the same day. Mastercard's fraud team wants to catch that pattern early.
Find every card and day where the card was used at 3 or more different merchants. Show the card_id, the payment_date and the number of different merchants as merchant_count.
Asked of
- Data Scientist
- ML Engineer
- Data Engineer
- Data Analyst
- AI Engineer
paymentsTable21 rows
| Column Name | Type |
|---|---|
| payment_id | BIGINT |
| merchant_id | BIGINT |
| card_id | BIGINT |
| amount | DOUBLE |
| payment_date | DATE |
| is_fraud | BOOLEAN |
paymentsExample Input
| payment_id | merchant_id | card_id | amount | payment_date | is_fraud |
|---|---|---|---|---|---|
| 8001 | 1 | 901 | 12.5 | 2024-07-01 | false |
| 8002 | 2 | 902 | 499 | 2024-07-01 | false |
| 8003 | 4 | 903 | 1 | 2024-07-01 | true |
| 8004 | 1 | 903 | 1 | 2024-07-01 | true |
| 8005 | 5 | 903 | 1 | 2024-07-01 | true |
| 8006 | 3 | 904 | 860 | 2024-07-02 | false |
| 8007 | 2 | 905 | 129.99 | 2024-07-02 | false |
| 8008 | 4 | 906 | 9.99 | 2024-07-02 | false |
| 8009 | 1 | 907 | 23.4 | 2024-07-02 | false |
| 8010 | 5 | 908 | 75 | 2024-07-03 | false |
Example Output
| card_id | payment_date | merchant_count |
|---|---|---|
| 903 | 2024-07-01 | 3 |
Explanation
On July 1, card 903 made three payments of 1.00, each at a different merchant: StreamPlus, QuickMart and FashionLane. That is exactly the pattern the fraud team is looking for, so it appears with a count of 3. No other card in the example was used at more than one merchant on the same day.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Mastercard
Difficulty
hard
Topic
aggregations
Your query
Starting the SQL engine...