Coding questions · Roblox
Write SQL to flag suspect payments and chargebacks
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.
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.
Flag suspect payments and chargebacks over recent windows
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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| birthdate | DATE | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
Sample data:
| birthdate | country | created_at | user_id |
|---|---|---|---|
| 2006-05-10 | US | 2025-05-27 13:00:00 | 1 |
| 2000-03-03 | US | 2022-02-14 10:00:00 | 2 |
| 2010-12-31 | CA | 2025-05-20 08:00:00 | 3 |
Table payments
| column | type | constraints |
|---|---|---|
| payment_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| amount_usd | NUMERIC(10,2) | NOT NULL |
| currency | VARCHAR(3) | NOT NULL |
| payment_ts | TIMESTAMP | NOT NULL |
| ip_country | VARCHAR(2) | NOT NULL |
| device_id | VARCHAR(50) | NOT NULL |
Sample data:
| amount_usd | currency | device_id | ip_country | payment_id | payment_ts | user_id |
|---|---|---|---|---|---|---|
| 4.99 | USD | A | US | 10 | 2025-05-28 12:00:00 | 1 |
| 12.99 | USD | A | US | 11 | 2025-05-28 12:30:00 | 1 |
| 19.99 | USD | B | MX | 12 | 2025-05-29 20:00:00 | 2 |
| 0.99 | CAD | C | CA | 13 | 2025-05-25 09:00:00 | 3 |
Table chargebacks
| column | type | constraints |
|---|---|---|
| cb_id | INT | PRIMARY KEY, NOT NULL |
| payment_id | INT | NOT NULL |
| cb_ts | TIMESTAMP | NOT NULL |
| reason_code | VARCHAR(100) | NOT NULL |
Sample data:
| cb_id | cb_ts | payment_id | reason_code |
|---|---|---|---|
| 100 | 2025-05-31 10:00:00 | 12 | FRAUD_CARDHOLDER_DISPUTE |
Expected output
One row per user who has at least one payment with payment_ts in [2025-05-25 00:00:00, 2025-06-01 00:00:00). payments_last_7d counts all such payments; suspect_cnt_last_7d counts those payments that meet any of the suspect criteria; any_chargeback_last_60d is 'Y' if the user has any chargeback tied to a payment with payment_ts in [2025-04-02 00:00:00, 2025-06-01 00:00:00) and cb_ts <= 2025-06-01, otherwise 'N'. On the sample data, all three users have payments in the last 7 days, and only user 2 has a qualifying chargeback.
| any_chargeback_last_60d | payments_last_7d | suspect_cnt_last_7d | user_id |
|---|---|---|---|
| N | 2 | 1 | 1 |
| Y | 1 | 1 | 2 |
| N | 1 | 1 | 3 |
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.