Roblox·Data Scientist·Technical Phone Screen·Senior

Senior

May 2026

Summary

Roblox data scientist interview with a pretty meaty SQL question that required thinking through deduplication from two different angles at once. The schema was straightforward but the edge cases were the whole point.

Questions Asked (1)

Using two tables, app_sessions and payments, write a single SQL query returning daily and country-level metrics (DAU, payers, revenue, ARPDAU, ARPPU) for the last 7 days. Join payments to sessions on both user_id and session_id. Handle duplicate session rows in app_sessions, multiple payment rows per session, and unmatched payments that have no corresponding session.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs

Author's notes

This one took me longer than I wanted to admit just to map out the dedup problem before writing a single line.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying metric definitions and edge cases, then outline a query structure using CTEs to deduplicate sessions, aggregate payments, and perform a full outer join to capture unmatched payments. Finally, compute daily and country-level metrics with careful handling of date ranges and nulls.

Pro tip: Explicitly state your assumptions about session deduplication (e.g., keeping the latest session per user per day) and how you handle unmatched payments, as these choices significantly impact metric accuracy and demonstrate product sense.

1. Clarify requirements and edge cases

Confirm definitions: DAU as distinct users with sessions, payers as distinct users with payments, revenue as sum of payment amounts, ARPDAU = revenue/DAU, ARPPU = revenue/payers. Discuss how to handle duplicate sessions, multiple payments per session, and unmatched payments.

2. Prepare deduplicated sessions and aggregated payments

Use CTEs to deduplicate app_sessions (e.g., row_number() over partition by user_id, session_id, date) and aggregate payments per session (sum amount, count transactions) to avoid fan-out.

3. Join sessions and payments with full outer join

Perform a FULL OUTER JOIN on user_id and session_id to include unmatched payments. Use COALESCE to align dates and countries from both tables, ensuring payments without sessions are counted.

4. Compute daily and country-level metrics

Group by date and country, then calculate DAU (distinct users from sessions), payers (distinct users from payments), revenue (sum of payment amounts), ARPDAU (revenue/DAU), and ARPPU (revenue/payers). Handle division by zero with NULLIF.

5. Filter last 7 days and finalize query

Apply a date filter (e.g., date >= current_date - interval '7 days') in the final SELECT or within CTEs, and ensure the query is efficient and readable with comments.

Key Points to Mention

Deduplication of app_sessions to avoid inflating DAU and revenue
Aggregation of payments per session before joining to prevent double-counting
Use of FULL OUTER JOIN to include unmatched payments and COALESCE for date/country
Definition and calculation of ARPDAU and ARPPU with NULLIF to avoid division by zero
Date filtering for last 7 days and handling of time zones if applicable
Potential data quality issues like missing country or null user_id

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