Write SQL to flag suspect payments and chargebacks
Company: Roblox
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: HR Screen
Assume today is 2025-09-01. Using only CASE WHEN and JOINs (no window functions required), write a single SQL query that produces, for each user with any payment in the last 7 days [2025-08-25 00:00:00, 2025-09-01 00:00:00), the columns: user_id, payments_last_7d, suspect_cnt_last_7d, any_chargeback_last_60d (Y/N). A payment is “suspect” if at least one holds: (a) user is a minor on the payment timestamp (<18 years), (b) users.country != payments.ip_country, or (c) payment occurred within 24 hours of the user’s account creation AND amount_usd > 10. any_chargeback_last_60d is Y if the user has any chargeback linked to any of their payments with payment_ts in [2025-07-03 00:00:00, 2025-09-01 00:00:00) and cb_ts ≤ 2025-09-01.
Schema and tiny sample data:
users
- user_id (int), birthdate (date), country (text), created_at (timestamp)
| user_id | birthdate | country | created_at |
|--------:|-------------|---------|----------------------|
| 1 | 2012-05-10 | US | 2023-11-01 09:00:00 |
| 2 | 2000-03-03 | US | 2022-02-14 10:00:00 |
| 3 | 2010-12-31 | CA | 2025-08-20 08:00:00 |
payments
- payment_id (int), user_id (int), amount_usd (numeric), currency (text), payment_ts (timestamp), ip_country (text), device_id (text)
| payment_id | user_id | amount_usd | currency | payment_ts | ip_country | device_id |
|-----------:|--------:|-----------:|---------|----------------------|------------|-----------|
| 10 | 1 | 4.99 | USD | 2025-08-28 12:00:00 | US | A |
| 11 | 1 | 12.99 | USD | 2025-08-28 12:30:00 | US | A |
| 12 | 2 | 19.99 | USD | 2025-08-31 20:00:00 | MX | B |
| 13 | 3 | 0.99 | CAD | 2025-08-25 09:00:00 | CA | C |
chargebacks
- cb_id (int), payment_id (int), cb_ts (timestamp), reason_code (text)
| cb_id | payment_id | cb_ts | reason_code |
|------:|-----------:|---------------------|-----------------------------|
| 100 | 12 | 2025-09-15 10:00:00 | FRAUD_CARDHOLDER_DISPUTE |
Edge conditions to handle: precise date bounds, age computed on payment_ts (birthdate+18 years), left joins so users without chargebacks still appear, and ensure only users with ≥1 payment in the last 7 days are returned.
Quick Answer: This question evaluates proficiency with SQL conditional logic (CASE WHEN), JOINs, date/time arithmetic, aggregation, and encoding business rules for flagging suspect payments and chargebacks, and is categorized under Data Manipulation (SQL/Python) for a Data Scientist role.