Summary
Meta DS technical screen, basically one big SQL problem broken into three parts. The schema wasn't too scary but the overlap logic and P90 requirement made it genuinely hard to keep in one clean query. Left feeling like I got maybe 70% of the way there.
Questions Asked(3)
The filtering part was fine, just a join on users with a couple of WHERE conditions.
Suggested Approach
Start by filtering out test users and users from the excluded email domain before any aggregation, then use a timeline-based sweep-line algorithm (or SQL window functions with start/end event unpivoting) to compute peak concurrent participants per call. Finally, label each call as a 'group call' based on whether its computed peak concurrency meets the threshold of 3 or more.
Filter & Clean the Data
Exclude all rows where the user is flagged as a test user or whose email domain matches the restricted domain using a WHERE clause or equivalent filter. This ensures downstream metrics are not polluted by non-production participants.
Unpack Intervals into Events
Transform each participation interval into two timestamped events: a +1 event at join_time and a -1 event at leave_time. This 'event stream' approach is the foundation of the sweep-line algorithm for computing concurrency.
Compute Running Concurrency per Call
Partition the event stream by call_id, order by timestamp, and compute a running sum of the +1/-1 values using a window function (SUM OVER) or an iterative sweep. Each running sum value represents the number of concurrent participants at that moment in time.
Derive Peak Concurrency per Call
Aggregate the running concurrency values by call_id using MAX to obtain the peak concurrent participant count for each call. This single value summarizes the busiest moment of each call.
Classify Group Calls & Summarize
Apply a CASE statement or boolean flag to label each call as a 'group call' if its peak concurrency is greater than or equal to 3. Optionally, report summary statistics such as the percentage of calls that are group calls or the distribution of peak concurrency values.
Key Points to Mention
P90 in ANSI SQL without PERCENTILE_CONT is annoying and I blanked for a second.
Suggested Approach
Treat this as a SQL-heavy data modeling problem by first identifying the grain of the source data (likely one row per call event or per call-participant-second), then applying the necessary aggregations and window functions to compute each metric at the calendar day level. Focus on clearly defining 'peak concurrency' before writing any query, as it requires a time-series approach distinct from simple counts.
Clarify Definitions & Schema
Confirm the schema (e.g., a calls table with call_id, start_time, end_time, is_group_call_enabled, participant counts) and pin down exact metric definitions — especially what constitutes a 'group call' and how peak concurrency is measured (e.g., max simultaneous active calls per day).
Apply the Core Filter
Filter the dataset to only include calls where group calling was enabled (e.g., WHERE is_group_call_enabled = TRUE) before performing any aggregation, ensuring all three metrics share the same filtered population.
Compute Daily Call Counts
Group by the calendar day of call start time to compute total calls started (COUNT(call_id)) and number of group calls (COUNT(CASE WHEN participant_count > 2 THEN 1 END) or a similar group-call flag), generating one row per day.
Calculate Peak Concurrency Per Call
Derive peak concurrency for each call using a timeline expansion approach — generate events for call start (+1) and call end (-1), compute a running sum ordered by timestamp, and take the maximum value per call_id as that call's peak concurrent participants.
Compute 90th Percentile & Assemble Final Output
Join the per-call peak concurrency back to the daily grain and use PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY peak_concurrency) to compute the 90th percentile per calendar day, then SELECT all three metrics alongside the date column.
Key Points to Mention
This was the part I ran out of time on.
Suggested Approach
Break the problem into two distinct sub-problems: first, compute peak concurrency per call using a self-join or window function approach to count overlapping calls, then rank and select the top 3. Second, for each of those top 3 calls, scan only the event timestamps within the first 10 minutes to find the earliest moment concurrency reached 3, returning NULL if it never does.
Clarify Schema & Assumptions
Ask about the table structure — confirm whether you have a calls table with call_id, start_time, end_time, and any metadata columns. Clarify the definition of 'started on a specific day' (UTC vs. local time) and whether calls can span midnight.
Compute Peak Concurrency Per Call
Use a self-join on the calls table (or a window function with UNBOUNDED frames) to count, for each call, the maximum number of other calls that were active simultaneously. A self-join approach: for call A, count all calls B where B.start_time < A.end_time AND B.end_time > A.start_time, then take the MAX grouped by call_id.
Rank and Select Top 3
Apply RANK() or DENSE_RANK() ordered by peak concurrency descending over the filtered set of calls that started on the target day, then filter to rank <= 3. Handle ties explicitly by deciding whether to use RANK (may return more than 3) or ROW_NUMBER (strict top 3).
Find First Timestamp Hitting Concurrency = 3 Within 10 Minutes
For each of the top 3 calls, generate candidate timestamps using the start_times of all calls that overlap within the first 10 minutes of the anchor call — these are the only moments concurrency can change. At each candidate timestamp, count concurrent calls and use MIN() with a HAVING or FILTER clause to find the earliest time concurrency >= 3, returning NULL if none exists.
Assemble Final Output
JOIN the top 3 calls with their metadata and LEFT JOIN the first-concurrency-3 timestamp result, so calls that never reach concurrency 3 in the window naturally produce NULL. Present the final SELECT with call_id, metadata columns, peak_concurrency, and first_concurrency_3_timestamp.
Key Points to Mention
Discussion(3)
Sign in to join the discussion.
The event expansion approach you used is the right one and Meta interviewers generally know it well, so you weren't fighting an uphill battle there. The thing that tripped me up the first time I wrote this pattern under pressure was the same window frame issue: people default to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW without thinking, which is fine, but you have to ORDER BY the timestamp column in the SUM() window or the running total is meaningless. The COALESCE(leave_ts, end_ts) detail is genuinely easy to forget and it's the kind of thing that silently breaks your concurrency count because anyone still on the call at the end just never gets a -1 event. For the filtering, the cleanest version I've seen does the domain exclusion and test user flag as a CTE before you even touch the event expansion, so your events table is already clean when you start the +1/-1 logic. Keeps the final window query readable. One thing worth practicing: after you get the running sum per call, you need MAX() over the whole call to get peak concurrency, which is just a GROUP BY call_id on top of the CTE. Then the group call label is a simple CASE WHEN peak >= 3. The pieces are all individually easy but stitching four CTEs together without losing track of what each one represents is where the clock kills you.
Five more minutes and you probably had it. The structure is: filter events to call_start_ts + interval '10' minute before doing the running sum, then MIN(event_ts) WHERE running_sum >= 3. That MIN with a filter on the already-computed running sum is where people get stuck because you can't filter on a window function in the same SELECT it's defined, so you need one more CTE wrapping the running sum result before you can apply the >= 3 condition.
The date spine problem is the one I'd focus on if you're prepping for a follow-up. Generating a spine without a numbers table or a recursive CTE is awkward in most SQL dialects, but in practice at Meta you'd be writing Presto/Trino, which has sequence() and unnest() so you can do something like unnest(sequence(date_start, date_end, interval '1' day)) as cal_date and then LEFT JOIN your calls onto that. Without that, people sometimes use a calendar table if the environment has one, or they just hope the interviewer accepts a note that "I'd generate a spine here." The P90 piece: PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY peak_concurrency) is standard SQL and Presto supports it, so if you're blanking on the row_number approach, that function is your escape hatch and it's one line. The row_number method works too but you have to be careful: you want the row where row_number = CEIL(0.9 * COUNT(*)) and you need a subquery or CTE to get the count before you can reference it in a WHERE. Describing the logic out loud and flagging uncertainty is genuinely fine in a DS screen, especially at Meta where they care about whether you can reason through a problem.