Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Aug 2025Remote

Summary

SQL screen for a Data Scientist role at Meta. One question, pretty involved, mixing DAU definitions with a multi-table join across users, events, and calls. Not the hardest thing ever but there are a few gotchas that'll trip you up if you're not careful.

Questions Asked (1)

Write a single SQL query to compute the percentage of French DAU (users with any event on 2025-08-31) who participated in at least one video call that same day, either as caller or callee. Return the denominator, numerator, and percentage as a decimal.

Product Analytics & MetricsData Modeling

Author's notes

The tricky part is the callee side.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, identify the denominator by counting distinct French users who had any event on 2025-08-31. Then, identify the numerator by counting distinct French users who were either caller or callee in at least one video call on that same day. Finally, compute the percentage as numerator divided by denominator, ensuring you use the same user base for both counts.

Pro tip: Clarify whether 'French' refers to the user's country attribute or the event's country; typically, it's the user's country. Also, consider using a single query with conditional aggregation to avoid multiple scans of large tables.

1. Define the denominator

Count distinct users with any event on 2025-08-31 who are French. This represents the daily active users (DAU) for that day.

2. Define the numerator

Count distinct French users who participated in at least one video call on 2025-08-31, either as caller or callee. This requires unioning caller and callee IDs from the video call events.

3. Combine and compute percentage

Use conditional aggregation or subqueries to compute both counts in a single query, then calculate the percentage as numerator divided by denominator.

4. Handle edge cases

Ensure that users with multiple events or multiple video calls are counted only once. Also, consider if there are users with no events but still considered DAU (unlikely).

Key Points to Mention

Use of DISTINCT to count unique users
Filtering by date and country (French users)
Union of caller and callee roles to capture all video call participants
Conditional aggregation (e.g., SUM(CASE WHEN ...)) to compute numerator and denominator in one pass
Handling of NULLs or missing data in user country or event tables
Efficiency considerations for large datasets (e.g., avoiding multiple subqueries if possible)

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