Merchants with high fraud rates
mediumMerchants with high fraud rates
Mastercard SQL Interview Question
Mastercard's fraud team reviews merchants where a large share of card payments turn out to be fraudulent.
Find every merchant where more than 30% of payments were fraudulent. Show the merchant_name, the number of payments as total_payments and the number of fraudulent payments as fraud_payments.
Asked of
- Data Analyst
- Data Scientist
- ML Engineer
- Analytics Engineer
paymentsTable21 rows
| Column Name | Type |
|---|---|
| payment_id | BIGINT |
| merchant_id | BIGINT |
| card_id | BIGINT |
| amount | DOUBLE |
| payment_date | DATE |
| is_fraud | BOOLEAN |
merchantsTable5 rows
| Column Name | Type |
|---|---|
| merchant_id | BIGINT |
| merchant_name | VARCHAR |
| category | VARCHAR |
| country | VARCHAR |
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 |
merchantsExample Input
| merchant_id | merchant_name | category | country |
|---|---|---|---|
| 1 | QuickMart | Convenience | United States |
| 2 | GadgetHub | Electronics | United States |
| 3 | SkyFly Travel | Travel | United Kingdom |
| 4 | StreamPlus | Digital Services | United States |
| 5 | FashionLane | Apparel | Germany |
Example Output
| merchant_name | total_payments | fraud_payments |
|---|---|---|
| FashionLane | 2 | 1 |
| QuickMart | 3 | 1 |
| StreamPlus | 2 | 1 |
Explanation
QuickMart took 3 payments in the example, and 1 of them was fraudulent, which is 33%, so it qualifies. StreamPlus and FashionLane each had 1 fraudulent payment out of 2. GadgetHub had no fraudulent payments, so it is left out.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Mastercard
Difficulty
medium
Topic
aggregations
Your query
Starting the SQL engine...