← Roblox Interview Insights

Roblox·Data Scientist·Technical Phone Screen·Senior

Senior
Jul 2026

Summary

Roblox data scientist interview with a pretty gnarly SQL question that tests your ability to handle deduplication logic, window functions, and multi-step aggregation all at once. One question, but it's a lot of question.

Questions Asked (1)

Q1

Write a SQL query to compute, for each campaign over a 7-day window, total impressions, total charged clicks (deduplicating multiple clicks on the same ad request to only count the earliest), CTR, RPM, and standard deviation of daily RPM. Filter to campaigns with at least 10,000 impressions and order by RPM descending then stddev ascending.

Product Analytics & MetricsData Modeling
Author's notes

The dedup rule is what makes this hard.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into stages: first deduplicate clicks to the earliest per ad request, then aggregate daily metrics per campaign, and finally compute the 7-day windowed totals, CTR, RPM, and standard deviation of daily RPM. Use window functions to handle the rolling window and apply the filters and ordering at the end.

Pro tip: Clarify the definition of RPM (revenue per mille) and confirm whether it's based on charged clicks or impressions; also mention that deduplication should happen before any aggregation to avoid double-counting revenue.

1. Deduplicate clicks

Use a window function like ROW_NUMBER() partitioned by ad_request_id and ordered by click_timestamp to select only the earliest click per ad request.

2. Aggregate daily metrics

Join impressions and deduplicated clicks, then group by campaign_id and date to compute daily impressions, charged clicks, and revenue (if available).

3. Compute 7-day rolling metrics

Use window functions with a 7-day range to sum impressions and clicks, and calculate CTR and RPM for each campaign over the window.

4. Calculate standard deviation of daily RPM

Within the 7-day window, compute the standard deviation of daily RPM using STDDEV or equivalent, ensuring it's calculated over the daily values.

5. Filter and order

Apply the HAVING clause to keep campaigns with total impressions >= 10000, then order by RPM descending and standard deviation ascending.

Key Points to Mention

  • Deduplication logic: use ROW_NUMBER() or MIN(click_timestamp) to ensure only the earliest click per ad request is counted.
  • Window frame specification: use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW to get a 7-day window including the current day.
  • CTR calculation: total clicks divided by total impressions, expressed as a percentage.
  • RPM calculation: (total revenue / total impressions) * 1000, assuming revenue is available; if not, clarify with the interviewer.
  • Standard deviation of daily RPM: compute daily RPM first, then apply STDDEV over the 7-day window.
  • Filtering and ordering: use HAVING for the impression threshold, and ORDER BY RPM DESC, stddev ASC.

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