Roblox·Data Scientist·Technical Phone Screen·Intermediate

IntermediatePrefer not to say

Jun 2026Remote

Summary

Roblox data scientist interview threw a pretty dense SQL question at me that combined fraud logic, age calculations, and chargeback lookups all in one query. No window functions allowed, which was the annoying constraint. Felt okay about the structure but definitely second-guessed some of the edge case handling afterward.

Questions Asked (1)

Using only CASE WHEN and JOINs (no window functions), write a single SQL query that returns, for each user with at least one payment in the last 7 days, their user_id, total payment count in that window, count of suspect payments in that window, and a Y/N flag for whether they have any chargeback on payments from the last 60 days. A payment is suspect if the user was under 18 at the time of payment, the user's country doesn't match the payment's ip_country, or the payment happened within 24 hours of account creation and exceeded $10. The chargeback flag covers any chargeback timestamped on or before today linked to payments in the 60-day window.

Data ModelingProduct Analytics & MetricsRoot Cause Analysis

Author's notes

This one took me a minute to even parse.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the schema and definitions (e.g., how to determine age, time windows, and chargeback linkage). Then, build the query in layers: filter payments to the last 7 days, compute the suspect flag using CASE WHEN, aggregate per user, and finally LEFT JOIN a subquery that checks for chargebacks on payments in the last 60 days. Use conditional aggregation (SUM(CASE WHEN ... THEN 1 ELSE 0 END)) to count suspects and set the Y/N flag.

Pro tip: Explicitly state your assumptions about the schema (e.g., users table has birthdate and country, payments table has ip_country and created_at, chargebacks table links to payments) and mention that you'd validate edge cases like timezone handling and null values. This shows you think about data quality and real-world implementation, not just query syntax.

1. Clarify schema and definitions

Ask about table structures, column names, and how to compute age at payment time, time windows (e.g., last 7 days from today), and chargeback linkage. Confirm that 'today' is the current date and that timestamps are in a consistent timezone.

2. Filter payments to last 7 days

Create a subquery or CTE that selects payments from the last 7 days, joining users to get birthdate and country. This reduces the dataset for aggregation.

3. Compute suspect flag and aggregate per user

Use CASE WHEN to flag each payment as suspect based on the three conditions. Then group by user_id and use conditional aggregation to count total payments and suspect payments.

4. Determine chargeback flag for last 60 days

In a separate subquery, join payments in the last 60 days to chargebacks (where chargeback timestamp <= today) and group by user_id to get a flag. Then LEFT JOIN this to the aggregated result and use CASE WHEN to output 'Y' or 'N'.

5. Combine and finalize query

Assemble the final query using JOINs and CASE WHEN only, ensuring no window functions. Verify that users with no chargebacks get 'N' and that only users with at least one payment in the last 7 days are included.

Key Points to Mention

Use of conditional aggregation (SUM(CASE WHEN ... THEN 1 ELSE 0 END)) to count suspect payments without window functions.
Handling of age calculation: e.g., using DATE_DIFF or equivalent to compute age at payment time from birthdate.
Time window logic: filtering payments between CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE, and similarly for 60 days.
Chargeback linkage: joining chargebacks to payments on payment_id and ensuring chargeback timestamp is <= today.
LEFT JOIN and COALESCE to ensure users without chargebacks get 'N' and are not dropped.
Assumptions about schema and data quality (e.g., null handling, timezone consistency) and how they affect the query.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.