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.
Overview: 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.
Read the full Roblox Data Scientist interview experience this question came from
Assume the current date is 2025-06-01. Using only CASE WHEN expressions and JOINs (no window functions required), write a single SQL query that returns, for each user who has at least one payment in the last 7 days (i.e., payment_ts in the range [2025-05-25 00:00:00, 2025-06-01 00:00:00)), the following columns:
- user_id
- payments_last_7d: count of that user's payments with payment_ts in [2025-05-25 00:00:00, 2025-06-01 00:00:00)
- suspect_cnt_last_7d: count of that user's payments in that same 7-day window that are "suspect"
- any_chargeback_last_60d: 'Y' if the user has at least one chargeback linked to any of their payments where the payment_ts is in [2025-04-02 00:00:00, 2025-06-01 00:00:00) and the chargeback timestamp cb_ts <= 2025-06-01; otherwise 'N'.
A payment is considered "suspect" if at least one of the following holds at the time of that payment:
(a) The user is a minor at the payment timestamp (payment_ts < birthdate + 18 years).
(b) users.country <> payments.ip_country.
(c) The payment occurred within 24 hours of the user's account creation (payment_ts < created_at + 24 hours) AND amount_usd > 10.
Edge conditions to handle:
- Use the exact timestamp bounds as specified (inclusive lower bound, exclusive upper bound for the time ranges).
- Compute age at the payment timestamp via birthdate + interval '18 years'.
- Use LEFT JOINs so that users without matching chargebacks can still appear.
- Ensure that only users with at least one payment in the last 7 days are returned.
Write a single SQL query that produces the requested columns using the schema and sample data below.
Tables
users(user_id INT, birthdate DATE, country VARCHAR(2), created_at TIMESTAMP)
payments(payment_id INT, user_id INT, amount_usd NUMERIC(10,2), currency VARCHAR(3), payment_ts TIMESTAMP, ip_country VARCHAR(2), device_id VARCHAR(50))
chargebacks(cb_id INT, payment_id INT, cb_ts TIMESTAMP, reason_code VARCHAR(100))
Hints
- Use conditional aggregation with SUM(CASE WHEN ...) to count payments in the different time windows and to flag suspect payments.
- Compute whether a user is a minor at payment time by comparing payment_ts with birthdate + INTERVAL '18 years', and use a HAVING clause to keep only users with at least one payment in the last 7 days.