Roblox·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jul 2026

Summary

SQL-heavy technical screen for a DS role at Roblox. Two questions, both around a social graph schema with users, posts, engagement events, and follows. Nothing behavioral, just pure query writing the whole time.

Questions Asked (2)

Given a social product schema with users, posts, engagement events, and follows tables, write a SQL query that computes each user's influence score, defined as the total number of engagement events their posts received plus their total follower count.

Product Analytics & MetricsData Modeling

Author's notes

My first instinct was to do both aggregations in one big join and i immediately tangled myself up with duplicate counts.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into two parts: first aggregate engagement events per user by joining posts to engagement events, then aggregate follower counts per user from the follows table. Combine these using a FULL OUTER JOIN or UNION to ensure users with only posts or only followers are included, and sum the counts to get the influence score.

Pro tip: Mention that you would handle users with no posts or no followers by using COALESCE to treat NULLs as zero, and consider whether to use a LEFT JOIN from users to both aggregates to include all users. Also, clarify that engagement events should be counted only for posts authored by the user, not for engagements the user made on others' posts.

1. Clarify schema and definitions

Confirm the table structures: users (user_id), posts (post_id, user_id), engagement_events (event_id, post_id, user_id?), follows (follower_id, followee_id). Define influence score as total engagement events on user's posts plus total followers.

2. Aggregate engagement events per user

Join posts to engagement_events on post_id, group by posts.user_id, and count the number of engagement events. This gives total engagements per user's posts.

3. Aggregate followers per user

From the follows table, group by followee_id (the user being followed) and count the number of followers. This gives total follower count per user.

4. Combine aggregates and compute influence score

Use a FULL OUTER JOIN or UNION of the two aggregates on user_id, then sum the engagement count and follower count, using COALESCE to handle NULLs as zero. Optionally, join back to users to include all users.

5. Validate and optimize

Check edge cases: users with no posts, no followers, or both. Consider indexing on foreign keys and using subqueries or CTEs for readability. Discuss potential performance implications.

Key Points to Mention

Use of LEFT JOIN or FULL OUTER JOIN to include users with zero engagements or zero followers.
COALESCE or IFNULL to treat NULL aggregates as zero.
Correct join condition: engagement_events should join to posts on post_id, not directly to users.
Grouping by the correct user identifier: posts.user_id for engagements, follows.followee_id for followers.
Consideration of whether to count distinct engagement events or all events (likely all).
Use of CTEs or subqueries for clarity and maintainability.

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

Using the same schema, write a SQL query that computes monthly follower growth per user, including new followers gained each month, the previous month's new follower count via LAG, a month-over-month growth rate with divide-by-zero handling, and a cumulative running total of followers.

Product Analytics & MetricsData Modeling

Author's notes

This one took me longer than i'd like to admit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, aggregate the raw follower events into monthly new follower counts per user. Then, use window functions (LAG for previous month, SUM OVER for cumulative total) and handle divide-by-zero in the growth rate calculation. Finally, ensure the output is ordered by user and month for readability.

Pro tip: Always clarify the grain of the data and the definition of 'monthly follower growth'—whether it's based on follow events or net changes—and confirm the expected output schema with the interviewer before writing the query.

1. Understand the schema and define metrics

Identify the table containing follower events (e.g., user_id, follower_id, follow_date) and clarify what 'new followers gained each month' means (e.g., count of distinct followers per user per month).

2. Aggregate monthly new followers

Write a subquery or CTE that groups by user and month, counting new followers. Use DATE_TRUNC or equivalent to extract the month from the follow date.

3. Apply window functions for LAG and cumulative sum

Use LAG to get the previous month's new follower count, and SUM OVER with an ORDER BY month to compute the running total of followers per user.

4. Calculate month-over-month growth rate with divide-by-zero handling

Compute (current_month - previous_month) / previous_month, using NULLIF or CASE to avoid division by zero when previous_month is 0 or NULL.

5. Finalize and order the output

Select the required columns (user_id, month, new_followers, prev_month_followers, growth_rate, cumulative_followers) and order by user_id and month for clarity.

Key Points to Mention

Use of window functions: LAG for previous month, SUM OVER for cumulative total.
Handling divide-by-zero with NULLIF or CASE to avoid errors.
Ensuring correct partitioning by user_id and ordering by month.
Clarifying the definition of 'new followers' (e.g., distinct followers per month).
Considering edge cases: first month (no previous month), zero previous month, and missing months.
Performance considerations: indexing on user_id and follow_date, and using CTEs for readability.

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