Meta·Data Scientist·Technical Phone Screen·Senior

Senior

Jul 2026Remote

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)

Given a call participation schema with overlapping time intervals, compute peak concurrent participants per call while excluding test users and users from a specific email domain. A call qualifies as a 'group call' if its peak concurrency reaches at least 3.

Product Analytics & MetricsAlgorithms & Data StructuresData Modeling

Author's notes

The filtering part was fine, just a join on users with a couple of WHERE conditions.

Create a free account to read the full note

AI HintsAI Generated

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.

Pro tip: Interviewers at Meta love when candidates proactively discuss edge cases like users who join and leave multiple times within the same call — clarify whether each session row is a separate interval or if you need to deduplicate overlapping sessions for the same user before computing concurrency.

1. 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.

2. 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.

3. 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.

4. 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.

5. 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

Sweep-line / event-based approach: converting intervals to +1/-1 events and using a running sum is more scalable than pairwise interval overlap joins (O(n) vs O(n²)).
Importance of filtering test users and restricted domains before any metric computation to avoid skewing results — mention doing this as a CTE or subquery for clarity.
Handling ties and boundary conditions: clarify whether a user leaving at the exact same timestamp as another joins counts as overlapping (open vs. closed interval semantics).
Deduplication of sessions: if a user can have multiple overlapping rows for the same call, decide whether to merge their intervals first to avoid double-counting a single participant.
SQL implementation using UNION ALL to combine start/end events, then SUM() OVER (PARTITION BY call_id ORDER BY event_time) for the running concurrency.
Business context awareness: explain why the 3-participant threshold for 'group call' matters for product decisions, such as feature gating, capacity planning, or engagement analysis.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

For each calendar day in a given date range, return the total calls started, number of group calls, and the 90th percentile of peak concurrency, filtering to calls where group calling was enabled.

Product Analytics & MetricsData Modeling

Author's notes

P90 in ANSI SQL without PERCENTILE_CONT is annoying and I blanked for a second.

Create a free account to read the full note

AI HintsAI Generated

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.

Pro tip: Proactively clarify ambiguities before coding — for example, ask whether 'calls started' means call initiation timestamp or the first participant join, and whether the date range is inclusive on both ends. Interviewers at Meta reward candidates who surface edge cases early rather than discovering them mid-solution.

1. 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).

2. 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.

3. 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.

4. 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.

5. 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

Use of PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY ...) or equivalent window function for the 90th percentile calculation
Timeline expansion technique (start/end event unpivoting with running SUM) to accurately compute concurrent call counts at any point in time
Importance of filtering on is_group_call_enabled before aggregation to ensure metric consistency across all three outputs
Handling of the date range boundary conditions — using BETWEEN or >= / <= on the call start timestamp and ensuring the calendar day series is complete (including days with zero calls)
Distinguishing between 'group call enabled' (a feature flag) and 'group call' (a call with more than 2 participants), as these may differ
Potential performance considerations such as partitioning by date and indexing on start_time for large-scale Meta datasets

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Return the top 3 calls by peak concurrency that started on a specific day, including call metadata and the first timestamp within the first 10 minutes of the call when concurrency first hit 3. Return NULL if it never reached 3 in that window.

Product Analytics & MetricsAlgorithms & Data Structures

Author's notes

This was the part I ran out of time on.

Create a free account to read the full note

AI HintsAI Generated

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.

Pro tip: Explicitly call out that 'concurrency at a timestamp' means counting all calls where start_time <= t < end_time, and clarify your assumptions about the data schema (e.g., whether you have a call_events table or just start/end times) before writing any SQL — interviewers reward candidates who surface ambiguity early.

1. 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.

2. 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.

3. 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).

4. 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.

5. 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

Concurrency change points only occur at call start_time events — scanning only these timestamps is O(n log n) rather than continuous, making the solution efficient.
The difference between RANK(), DENSE_RANK(), and ROW_NUMBER() when handling ties in peak concurrency, and which is appropriate given business requirements.
Using a self-join vs. an unpivot/event-stream approach (converting start/end into +1/-1 events and using a running SUM) — the event-stream approach scales better for large datasets.
The 10-minute window constraint means filtering candidate timestamps to anchor_call.start_time + INTERVAL '10 minutes', and only counting calls active at those moments.
NULL handling: using LEFT JOIN or CASE WHEN to explicitly return NULL when concurrency never reaches 3 in the window, rather than omitting the row.
Indexing considerations — mention that indexes on start_time and end_time would be critical for performance in a production setting with millions of call records.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.