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.
Quick Answer: 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.