Meta·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Apr 2026

Summary

Meta DS interview with two SQL problems built around a video-calling app scenario. Both questions came from the same pair of tables and the difficulty was less about syntax and more about not double-counting users across caller and recipient columns.

Questions Asked (2)

Given a call_logs table and a user_profile table, write a SQL query that returns the percentage of French users who made or received at least one call yesterday.

Product Analytics & MetricsData Modeling

Author's notes

The double-join tripped me up a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the schema and definitions: identify the user ID, country, and call timestamp columns, and define 'yesterday' in the appropriate timezone. Then, compute the set of French users and the set of users with at least one call yesterday, and calculate the ratio of their intersection to the total French users.

Pro tip: Mention that you would validate the result by checking edge cases like users with no calls, calls spanning midnight, and timezone consistency, and consider whether to use a LEFT JOIN or EXISTS for performance.

1. Clarify schema and definitions

Confirm the column names and data types in call_logs and user_profile, and define 'yesterday' in the relevant timezone (e.g., UTC or user's local time).

2. Identify French users

Filter user_profile to get the set of user IDs where country = 'France' (or equivalent).

3. Identify users with calls yesterday

From call_logs, select distinct user IDs where the call timestamp falls within yesterday, considering both caller and receiver columns if separate.

4. Compute the percentage

Count French users who appear in the yesterday-call set, divide by total French users, and multiply by 100 to get the percentage.

5. Write and validate the query

Construct the SQL using CTEs or subqueries, and mentally test with edge cases (e.g., no French users, no calls yesterday) to ensure correctness.

Key Points to Mention

Handling timezones: ensure 'yesterday' is defined consistently, possibly using UTC or the user's local timezone.
Using DISTINCT or GROUP BY to avoid double-counting users who made multiple calls.
Considering both caller and receiver columns if the call_logs table has separate fields for each.
Using LEFT JOIN or NOT EXISTS to include French users with zero calls, ensuring the denominator is correct.
Performance considerations: indexing on user_id and call timestamp, and avoiding unnecessary subqueries.
Edge cases: users with no calls, calls exactly at midnight, and users with missing country data.

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

Using the same tables, write a SQL query that computes the average total call duration per U.S. daily-active user for today.

Product Analytics & MetricsData Modeling

Author's notes

Simpler than the first one.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, identify the tables and columns needed: a calls table with user_id, call_duration, and call_date, and a users table with user_id and country. Then, filter for U.S. users and today's calls, aggregate total call duration per user, and finally compute the average across those users. Ensure you handle users with zero calls by deciding whether to include them (likely not, as 'daily-active' implies they made calls).

Pro tip: Clarify the definition of 'daily-active user'—does it mean users who made at least one call today, or users who were active on the platform today? This affects whether you need to join with an activity table. Also, consider time zones: 'today' should be based on the user's local time or a specific time zone like UTC?

1. Identify relevant tables and columns

Determine which tables contain the necessary data: a calls table with user_id, call_duration, and call_date; and a users table with user_id and country. Confirm that 'U.S.' refers to users with country = 'US'.

2. Filter for U.S. users and today's calls

Join the calls and users tables on user_id, filter for country = 'US' and call_date = CURRENT_DATE (or equivalent). This ensures only calls made today by U.S. users are considered.

3. Aggregate total call duration per user

Group by user_id and sum call_duration to get each user's total call duration for today. This creates a subquery or CTE with one row per active user.

4. Compute average across users

Calculate the average of the total durations from the previous step. Use AVG(total_duration) on the aggregated data to get the average total call duration per U.S. daily-active user.

Key Points to Mention

Definition of 'daily-active user': likely users who made at least one call today, but could also mean users active on the platform (requiring an activity table).
Time zone considerations: 'today' should be defined consistently, possibly using UTC or the user's local time zone.
Handling users with zero calls: if 'daily-active' includes users with no calls, you need to left join and use COALESCE to treat missing durations as 0.
SQL syntax: use of CTEs or subqueries for clarity, and proper aggregation functions (SUM, AVG).
Data quality: ensure call_duration is numeric and not null; consider filtering out invalid or test calls.
Performance: if tables are large, filter early and use indexes on user_id and call_date.

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