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.
Overview: 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.
Initiators with >3 distinct callees (completed video calls, last 7 days)
Assume UTC timestamps and use the explicit window 2025-08-26 00:00:00 through 2025-09-01 23:59:59 (inclusive).
Return a single integer: the number of DISTINCT users who initiated video calls where, across ALL video calls they initiated in this window, they interacted with MORE THAN 3 DISTINCT other users (callees).
Rules:
- Only include calls with call_type='video' AND status='completed'.
- Count unique callees across all qualifying calls started by the initiator within the window.
- Exclude the initiator themself from the callee count.
- Ignore test accounts (users.is_test = true) both as initiators and as callees.
- Your logic should be robust to duplicates in call_participants (e.g., a user joins the same call multiple times).
Tables
users(user_id INT, country_code CHAR(2), created_at DATE, is_test BOOLEAN)
calls(call_id INT, initiator_user_id INT, call_type VARCHAR(10), status VARCHAR(10), started_at TIMESTAMP, ended_at TIMESTAMP)
call_participants(call_id INT, user_id INT, role VARCHAR(10), joined_at TIMESTAMP, left_at TIMESTAMP)
events(event_id INT, user_id INT, event_type VARCHAR(50), event_ts TIMESTAMP)
Hints
- Use COUNT(DISTINCT callee_user_id) to protect against duplicate participant rows or multiple joins.
- Filter to completed video calls within the explicit timestamp window before counting callees.
Percent of FR DAUs who were on a video call yesterday (interval overlap + dedup joins)
Assume UTC timestamps.
Compute, for the day 2025-08-31 (00:00:00 through 23:59:59 inclusive):
- DAU_FR: DISTINCT users in France (users.country_code='FR') with users.is_test=false who generated ANY event on 2025-08-31.
- ONCALL_FR: DISTINCT users in France (users.country_code='FR') with users.is_test=false who participated (initiator OR callee) in ANY completed video call where their participant interval [joined_at, left_at] overlaps the day (i.e., interval overlap between [joined_at, left_at] and [2025-08-31 00:00:00, 2025-08-31 23:59:59]).
Return a single row with:
- numerator (ONCALL_FR)
- denominator (DAU_FR)
- percentage_oncall_fr (numerator/denominator*100) rounded to 2 decimals
Your query must safeguard against double-counting a user who has multiple call_participants rows for the same call (e.g., reconnects).
Tables
users(user_id INT, country_code CHAR(2), created_at DATE, is_test BOOLEAN)
calls(call_id INT, initiator_user_id INT, call_type VARCHAR(10), status VARCHAR(10), started_at TIMESTAMP, ended_at TIMESTAMP)
call_participants(call_id INT, user_id INT, role VARCHAR(10), joined_at TIMESTAMP, left_at TIMESTAMP)
events(event_id INT, user_id INT, event_type VARCHAR(50), event_ts TIMESTAMP)
Hints
- Interval overlap for a day can be checked with: joined_at <= day_end AND left_at >= day_start.
- Use DISTINCT user_id (or DISTINCT call_id, user_id) to avoid counting reconnects multiple times.
Community answers
Answer by SS
For the second question:
WITH dau_fr AS (
SELECT DISTINCT u.user_id
FROM users u
JOIN events e
ON u.user_id = e.user_id
WHERE u.country_code = 'FR'
AND u.is_test = false
AND e.event_ts >= TIMESTAMP '2025-08-31 00:00:00'
AND e.event_ts <= TIMESTAMP '2025-08-31 23:59:59'
),
oncall_fr AS (
SELECT DISTINCT
u.user_id,
cp.call_id
FROM users u
JOIN call_participants cp
ON u.user_id = cp.user_id
JOIN calls c
ON cp.call_id = c.call_id
WHERE u.country_code = 'FR'
AND u.is_test = false
AND c.call_type = 'video'
AND c.status = 'completed'
-- Proper interval overlap
AND cp.joined_at < TIMESTAMP '2025-09-01 00:00:00'
AND cp.left_at > TIMESTAMP '2025-08-31 00:00:00'
),
final AS (
SELECT
(SELECT COUNT(DISTINCT user_id) FROM oncall_fr) AS oncall_fr,
(SELECT COUNT(DISTINCT user_id) FROM dau_fr) AS dau_fr
)
SELECT
oncall_fr,
dau_fr,
ROUND(oncall_fr * 100.0 / NULLIF(dau_fr, 0), 2) AS pct_oncall_fr
FROM final;