Write SQL for video-call recipients and FR activity
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given the schema and samples below, write ANSI‑SQL to answer both questions. Assume dates are stored in UTC. Today is 2025-09-01, so “yesterday” is 2025-08-31 and the “last 7 days” window is 2025-08-25 through 2025-08-31 inclusive (exclude 2025-09-01).
Tables
- video_calls(date DATE, caller_id STRING, recipient_id STRING, call_id BIGINT, duration_sec INT)
- daily_users(date DATE, user_id STRING, country STRING, dau_flag TINYINT)
Small ASCII samples (not exhaustive)
video_calls
| date | caller_id | recipient_id | call_id | duration_sec |
|------------|-----------|--------------|---------|--------------|
| 2025-08-25 | U1 | U2 | 1 | 300 |
| 2025-08-25 | U1 | U3 | 2 | 120 |
| 2025-08-25 | U2 | U1 | 3 | 60 |
| 2025-08-26 | U1 | U2 | 4 | 200 |
| 2025-08-27 | U3 | U4 | 5 | 400 |
| 2025-08-27 | U1 | U4 | 6 | 180 |
| 2025-08-30 | U5 | U1 | 7 | 240 |
| 2025-08-30 | U1 | U5 | 8 | 60 |
| 2025-08-31 | U2 | U5 | 9 | 300 |
| 2025-08-31 | U4 | U1 | 10 | 100 |
| 2025-08-31 | U6 | U6 | 11 | 30 |
| 2025-09-01 | U1 | U2 | 12 | 90 |
daily_users
| date | user_id | country | dau_flag |
|------------|---------|---------|----------|
| 2025-08-31 | U1 | FR | 1 |
| 2025-08-31 | U2 | FR | 1 |
| 2025-08-31 | U3 | FR | 0 |
| 2025-08-31 | U4 | US | 1 |
| 2025-08-31 | U5 | FR | 1 |
| 2025-08-31 | U6 | FR | 1 |
| 2025-09-01 | U1 | FR | 1 |
| 2025-09-01 | U2 | FR | 1 |
Q1) Find the top 10 callers by the number of distinct recipients they called in the last 7 days (2025-08-25..2025-08-31). Exclude self‑calls (caller_id = recipient_id). Break ties by total calls in the window, then by caller_id ascending. Return: caller_id, distinct_recipient_count, total_calls.
Q2) What percentage of active daily users in France were on at least one call yesterday (2025-08-31), counting users who either called or received? Numerator: distinct users in FR with dau_flag = 1 on 2025-08-31 who appear as caller or recipient in video_calls on 2025-08-31. Denominator: distinct users with dau_flag = 1 and country = 'FR' in daily_users on 2025-08-31. Return: numerator, denominator, pct_active_on_call. Ensure no double‑counting of users who both called and received.
Overview: This question evaluates a candidate's ability to author ANSI-SQL for time-windowed aggregations, distinct recipient counts, tie-breaking sorts, joins with user tables, deduplication of users, and percentage calculations on activity data.
Read the full Meta Data Scientist interview experience this question came from
Top callers by distinct recipients over the last 7 days
You are given a table of video calls. Write ANSI SQL to find the top 10 callers by the number of distinct recipients they called in the 7-day window FROM 2025-05-25 TO 2025-05-31 (inclusive). Exclude self-calls where caller_id = recipient_id.
Rank callers by:
1) distinct_recipient_count (descending)
2) total_calls in the window (descending)
3) caller_id (ascending)
Return columns: caller_id, distinct_recipient_count, total_calls.
Tables
video_calls(date DATE, caller_id VARCHAR(10), recipient_id VARCHAR(10), call_id BIGINT, duration_sec INT)
Hints
- Filter to the date window first, then aggregate by caller_id.
- Use COUNT(DISTINCT recipient_id) and exclude rows where caller_id = recipient_id.
Percent of FR daily active users who were on a call yesterday
Write a PostgreSQL query. You are given:
- daily_users: one row per user per day with their country and whether they were active (dau_flag = 1)
- video_calls: one row per call with caller and recipient
Assume dates are stored in UTC and use the fixed date rules below.
Compute, for yesterday = 2025-05-31:
- Denominator: distinct users with country = 'FR' and dau_flag = 1 in daily_users on 2025-05-31.
- Numerator: among those denominator users, the distinct users who appear on at least one call on 2025-05-31 as either a caller or a recipient.
Return: numerator, denominator, pct_active_on_call (as a percentage). Ensure users who both called and received are only counted once in the numerator.
Tables
video_calls(date DATE, caller_id VARCHAR(10), recipient_id VARCHAR(10), call_id BIGINT, duration_sec INT)
daily_users(date DATE, user_id VARCHAR(10), country CHAR(2), dau_flag SMALLINT)
Hints
- Build the set of users on calls using a UNION of caller_id and recipient_id to avoid double-counting.
- Compute numerator as the intersection of (FR active users) and (users on calls).
Community answers
Answer by olayemioladapo1
Q1). SELECT
caller_id,
COUNT(DISTINCT recipient_id) AS distinct_recipient_count,
COUNT(*) AS total_calls
FROM video_calls
WHERE date BETWEEN '2025-08-25' AND '2025-08-31'
AND caller_id != recipient_id
GROUP BY caller_id
ORDER BY distinct_recipient_count DESC, total_calls DESC, caller_id ASC
LIMIT 10
Q1). WITH user_totals AS (
SELECT user_id, category, SUM(amount) AS total_spend
FROM purchases
GROUP BY user_id, category
),
category_avgs AS (
SELECT category, AVG(total_spend) AS avg_spend
FROM user_totals
GROUP BY category
)
SELECT u.user_id, u.category, u.total_spend
FROM user_totals u
JOIN category_avgs c ON u.category = c.category
WHERE u.total_spend > c.avg_spend