Compute video-call SQL metrics with edge cases
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Use 'today' = 2025-09-01. Assume UTC timestamps. Write SQL to answer both parts below and call out how your queries handle edge cases (duplicates, failed calls, users joining multiple times, calls spanning midnight, test accounts). Schema and small sample data are provided.
Schema:
- users(user_id INT PK, country_code CHAR(2), created_at DATE, is_test BOOL)
- calls(call_id INT PK, initiator_user_id INT, call_type ENUM('video','audio'), status ENUM('completed','missed','failed'), started_at TIMESTAMP, ended_at TIMESTAMP)
- call_participants(call_id INT, user_id INT, role ENUM('initiator','callee'), joined_at TIMESTAMP, left_at TIMESTAMP)
- events(event_id INT PK, user_id INT, event_type VARCHAR, event_ts TIMESTAMP)
Sample tables (subset):
users
user_id | country_code | created_at | is_test
1 | FR | 2025-07-01 | false
2 | FR | 2025-08-10 | false
3 | US | 2025-06-20 | false
4 | FR | 2025-08-25 | false
5 | DE | 2025-08-28 | false
calls
call_id | initiator_user_id | call_type | status | started_at | ended_at
10 | 1 | video | completed | 2025-08-30 10:00:00 | 2025-08-30 10:30:00
11 | 1 | video | failed | 2025-08-31 09:00:00 | 2025-08-31 09:05:00
12 | 2 | video | completed | 2025-09-01 12:00:00 | 2025-09-01 12:20:00
13 | 3 | audio | completed | 2025-08-31 13:00:00 | 2025-08-31 13:10:00
call_participants
call_id | user_id | role | joined_at | left_at
10 | 1 | initiator | 2025-08-30 10:00:00 | 2025-08-30 10:30:00
10 | 2 | callee | 2025-08-30 10:02:00 | 2025-08-30 10:30:00
10 | 4 | callee | 2025-08-30 10:05:00 | 2025-08-30 10:20:00
11 | 1 | initiator | 2025-08-31 09:00:00 | 2025-08-31 09:01:00
11 | 3 | callee | 2025-08-31 09:00:00 | 2025-08-31 09:00:10
12 | 2 | initiator | 2025-09-01 12:00:00 | 2025-09-01 12:20:00
12 | 3 | callee | 2025-09-01 12:00:05 | 2025-09-01 12:20:00
13 | 3 | initiator | 2025-08-31 13:00:00 | 2025-08-31 13:10:00
13 | 5 | callee | 2025-08-31 13:00:05 | 2025-08-31 13:10:00
events
event_id | user_id | event_type | event_ts
100 | 1 | app_open | 2025-08-31 08:55:00
101 | 2 | app_open | 2025-08-31 09:10:00
102 | 3 | app_open | 2025-08-31 13:00:00
103 | 4 | app_open | 2025-08-31 20:00:00
Tasks:
A) How many distinct users initiated video calls with more than 3 different other users during the last 7 days inclusive of today, i.e., 2025-08-26 00:00:00 through 2025-09-01 23:59:59? Count callees across all video calls they started in that window; exclude the initiator themself; ignore test accounts; include only calls with status='completed'. Return a single integer.
B) What percentage of DAUs from France were on a video call yesterday (2025-08-31)? Define DAU_FR as distinct users with users.country_code='FR', is_test=false, who generated any events on 2025-08-31 (events.event_ts in [2025-08-31 00:00:00, 2025-08-31 23:59:59]). Define ONCALL_FR as distinct users in FR who participated in any video call (initiator or callee) with any overlap with 2025-08-31 (interval overlap between [joined_at, left_at] and the day). Use status='completed'. Return a single row with numerator, denominator, and percentage to two decimals. Also provide a version that safeguards against double-counting users who join multiple times in the same call.
Quick Answer: This question evaluates the ability to manipulate time-series and relational call/event data using SQL (and optionally Python), emphasizing aggregation, DISTINCT counting, interval-overlap logic, and handling edge cases such as duplicates, failed calls, users joining multiple times, calls spanning midnight, and test accounts.