Write SQL to analyze group-call concurrency
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given call data and must compute group-call metrics. Schema (timestamps are UTC):
Tables:
- calls(call_id INT PRIMARY KEY, host_user_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, is_group_enabled BOOLEAN)
- call_participants(call_id INT, user_id INT, join_ts TIMESTAMP, leave_ts TIMESTAMP NULL)
- users(user_id INT PRIMARY KEY, email TEXT, country TEXT, is_test BOOLEAN)
Sample rows:
users
user_id | email | country | is_test
10 | a@alpha.com | US | false
11 | b@example.com | US | true
12 | c@alpha.com | US | false
13 | d@beta.com | CA | false
14 | e@alpha.com | US | false
15 | f@alpha.com | US | false
calls
call_id | host_user_id | start_ts | end_ts | is_group_enabled
1 | 10 | 2025-08-31 09:00:00 | 2025-08-31 09:30:00 | true
2 | 11 | 2025-09-01 10:00:00 | 2025-09-01 10:45:00 | true
3 | 10 | 2025-09-01 11:00:00 | 2025-09-01 11:07:00 | true
call_participants
call_id | user_id | join_ts | leave_ts
1 | 10 | 2025-08-31 09:00:00 | 2025-08-31 09:30:00
1 | 12 | 2025-08-31 09:02:00 | 2025-08-31 09:15:00
1 | 13 | 2025-08-31 09:04:00 | 2025-08-31 09:20:00
1 | 14 | 2025-08-31 09:05:00 | NULL
2 | 11 | 2025-09-01 10:00:00 | 2025-09-01 10:45:00
2 | 12 | 2025-09-01 10:02:00 | 2025-09-01 10:10:00
2 | 13 | 2025-09-01 10:02:00 | 2025-09-01 10:40:00
2 | 14 | 2025-09-01 10:15:00 | 2025-09-01 10:30:00
2 | 15 | 2025-09-01 10:33:00 | 2025-09-01 10:42:00
3 | 10 | 2025-09-01 11:00:00 | 2025-09-01 11:07:00
3 | 12 | 2025-09-01 11:06:00 | 2025-09-01 11:07:00
Tasks (write SQL; one query if possible, CTEs allowed):
1) Define a call’s peak concurrent participants as the maximum number of overlapping participant intervals within [start_ts, end_ts], where each participant interval is [join_ts, COALESCE(leave_ts, end_ts)]. Exclude test users (users.is_test = true) and any user whose email domain is 'example.com' from both host and participant counts. A call is a "group call" if its peak concurrency ≥ 3.
2) For each calendar day in 2025-08-26 through 2025-09-01 (inclusive; treat "today" as 2025-09-01), return: day, total calls started that day, number of group calls started that day, and the 90th percentile (P90) of peak concurrency among calls started that day. Only include calls where is_group_enabled = true.
3) Additionally, return the top 3 calls (by peak concurrency) that started on 2025-09-01 with: call_id, host_user_id, start_ts, peak_concurrency, and the first timestamp when concurrency first reached 3 within the first 10 minutes of the call (NULL if never reached).
Constraints/edge cases to handle explicitly in SQL: overlapping intervals, NULL leave_ts, hosts or participants filtered by test/email-domain rules, and ties in top-3 broken by earlier start_ts then smaller call_id. Explain your approach to computing overlaps (e.g., +1/-1 event expansion with running SUM) and to computing P90 in ANSI SQL.
Overview: This question evaluates advanced SQL competency in temporal interval analysis, concurrency counting, percentile aggregation, and complex user-filtering logic, testing skills in data manipulation, analytic/window functions, and performance-aware querying.
Compute peak concurrent participants per call
You are given three tables with call and participant data. For each call where is_group_enabled = TRUE, compute the call's peak concurrent participants and determine whether it is a group call.
Definitions and rules:
- A participant's effective interval for a call is [join_ts, COALESCE(leave_ts, end_ts)], clamped to the call's [start_ts, end_ts] window.
- A call's peak concurrent participants is the maximum number of overlapping participant intervals at any instant within the call's [start_ts, end_ts].
- Exclude all test users (users.is_test = TRUE) and any user whose email domain is 'example.com' from the participant counts. This exclusion applies to both hosts and non-host participants, but calls themselves are not filtered out by host type.
- A call is a "group call" if its peak concurrency is greater than or equal to 3.
Task: Write a single SQL query (you may use CTEs) that returns one row per call with is_group_enabled = TRUE, with the columns:
- call_id
- peak_concurrency (an integer)
- is_group_call (BOOLEAN, TRUE if peak_concurrency >= 3, otherwise FALSE)
Explain your approach to computing overlapping intervals in SQL (for example, by expanding each interval into +1 / -1 events and using a running SUM).
Tables
users(user_id INT, email VARCHAR(255), country VARCHAR(2), is_test BOOLEAN)
calls(call_id INT, host_user_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, is_group_enabled BOOLEAN)
call_participants(call_id INT, user_id INT, join_ts TIMESTAMP, leave_ts TIMESTAMP)
Hints
- First filter out test users and example.com emails by joining call_participants to users.
- To get concurrency, turn each interval into +1 (join) and -1 (leave) events and use a running SUM window function per call.
Daily group-call metrics and P90 peak concurrency
Using the same tables and definitions as in Question 1, you now need a daily summary of call activity.
Definitions and rules:
- Treat "today" as 2025-09-01.
- Consider only calls where is_group_enabled = TRUE.
- A call's peak concurrent participants and group-call definition are as in Question 1 (using filtered participants and overlap logic).
Task: For each calendar day from 2025-08-26 through 2025-09-01 (inclusive), return one row with:
- day (DATE)
- total_calls_started: number of calls with start_ts on that day
- group_calls_started: number of those calls that are group calls (peak_concurrency >= 3)
- p90_peak_concurrency: the 90th percentile (P90) of peak_concurrency among calls started that day
Include days even if there are zero calls (in that case, total_calls_started and group_calls_started should be 0, and p90_peak_concurrency should be NULL). Use only calls where is_group_enabled = TRUE.
Explain briefly how you compute the P90 in ANSI SQL (for example, by using an ordered-set aggregate such as PERCENTILE_CONT or an equivalent window-function approach).
Tables
users(user_id INT, email VARCHAR(255), country VARCHAR(2), is_test BOOLEAN)
calls(call_id INT, host_user_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, is_group_enabled BOOLEAN)
call_participants(call_id INT, user_id INT, join_ts TIMESTAMP, leave_ts TIMESTAMP)
Hints
- Put `RECURSIVE` immediately after `WITH` when any CTE in the list is recursive.
- Create a date spine so days without calls return zero counts and NULL P90.
Top calls and first time reaching 3-way concurrency
Using the same tables and concurrency definition as before, analyze calls that started on 2025-09-01.
Definitions and rules:
- Consider only calls where is_group_enabled = TRUE.
- Peak concurrent participants and participant filtering (test users and 'example.com' domain) are as in Question 1.
- For each call, define first_reach_3_ts as the earliest timestamp at which the call's concurrency first reached 3 participants, but only if this happens within the first 10 minutes after start_ts. If concurrency never reaches 3 within the first 10 minutes, this value should be NULL.
Task: Write a SQL query that returns the top 3 calls (by peak_concurrency) that started on 2025-09-01, with the columns:
- call_id
- host_user_id
- start_ts
- peak_concurrency
- first_reach_3_ts (as defined above)
If fewer than 3 calls started on that date, return all of them. Order the result by:
1) peak_concurrency descending,
2) start_ts ascending,
3) call_id ascending (as a final tie-breaker).
Use CTEs if needed, and explain in comments how you compute the time when concurrency first reaches 3.
Render `start_ts` and `first_reach_3_ts` as `YYYY-MM-DD HH24:MI:SS`; `first_reach_3_ts` remains NULL if concurrency never reaches 3 in the first 10 minutes.
Tables
users(user_id INT, email VARCHAR(255), country VARCHAR(2), is_test BOOLEAN)
calls(call_id INT, host_user_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, is_group_enabled BOOLEAN)
call_participants(call_id INT, user_id INT, join_ts TIMESTAMP, leave_ts TIMESTAMP)
Hints
- Reuse the event-expansion and running SUM approach to get a full concurrency timeline per call.
- Compute the MIN(event_ts) where concurrency >= 3 and event_ts <= start_ts + INTERVAL '10 minutes' for each call, then join this to per-call peak_concurrency and apply the requested ordering and TOP 3.