Compute video-call SQL metrics with edge cases
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.
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.