← Roblox Interview Insights

Roblox·Data Scientist·Technical Phone Screen·Senior

Senior
May 2026

Summary

SQL-heavy technical screen for a Data Scientist role at Roblox. One question, but it had real teeth: friendship deduplication across two event tables with validity constraints, plus indexing discussion for billion-row scale.

Questions Asked (1)

Q1

Given two tables, friend_requests and friend_accepts, write a single PostgreSQL query that returns unique undirected friendships with the earliest valid friendship date. A valid acceptance must have an accept_date on or after the matching request_date. Output one row per pair using LEAST/GREATEST to normalize direction, deduplicate pairs where both users sent requests to each other, and explain how you'd index these tables for a billion-row dataset.

Data ModelingSystem DesignTechnical Trade-offs
Author's notes

This looked like a join question until it wasn't.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and edge cases, then outline the SQL logic: join friend_requests and friend_accepts on matching user pairs with accept_date >= request_date, normalize direction using LEAST/GREATEST, and select the earliest accept_date per undirected pair. Finally, discuss indexing strategies for a billion-row dataset, focusing on composite indexes and partitioning.

Pro tip: Mention that you would first check for data quality issues like duplicate requests or accepts before writing the final query, and consider using a CTE to filter valid accepts early to reduce the join size.

1. Clarify Schema and Requirements

Ask about the exact columns, data types, and whether there can be multiple requests/accepts per pair. Confirm that 'valid' means accept_date >= request_date and that friendships are undirected.

2. Design the Query Logic

Use a CTE to join requests and accepts on matching user pairs with the date condition, then normalize direction with LEAST/GREATEST and select MIN(accept_date) per undirected pair.

3. Handle Deduplication and Edge Cases

Ensure that if both users sent requests to each other, only one row is returned. Use DISTINCT or GROUP BY on the normalized pair and consider ties in accept_date.

4. Optimize for Scale

Propose indexing strategies: composite indexes on (sender_id, receiver_id, request_date) and (accepter_id, requester_id, accept_date), and consider partitioning by date or user hash for a billion-row dataset.

5. Validate and Explain Trade-offs

Discuss potential performance bottlenecks, such as the cost of LEAST/GREATEST and sorting, and suggest alternatives like materialized views or pre-aggregation if needed.

Key Points to Mention

  • Use of LEAST and GREATEST to normalize undirected pairs
  • Filtering accepts with accept_date >= request_date before joining
  • Deduplication via GROUP BY on normalized pair and MIN(accept_date)
  • Composite indexes on join keys and date columns for efficient lookups
  • Partitioning strategies for billion-row tables (e.g., by date or hash)
  • Consideration of data quality issues like duplicate or invalid records

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