Write SQL to infer group-call demand
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given only 1:1 call logs and a user table. Use SQL to estimate latent demand for a 'Group Call' feature by detecting 10-minute 'call loops' where ≥3 distinct users are connected via overlapping or back-to-back 1:1 calls. Compute results for the last 7 days ending today (define today as 2025-09-01). Tasks: (a) Build an undirected edge view of calls (treat caller/callee symmetrically). Explain precisely when to use UNION vs UNION ALL and the pitfalls of deduplication. (b) Sessionize calls into rolling 10-minute windows and use a recursive CTE to find connected components ("loop sessions"). (c) Output, per day, the number of loop sessions, unique users in loops, and an 'unmet connectivity' metric per session = n*(n-1)/2 − observed_unique_pairs, then aggregate the metric per day. (d) Ensure calls with connected=0 are excluded from edges but may indicate failed attempts in a sensitivity variant—briefly describe how you would incorporate them. Required output columns: event_date, loop_sessions, loop_users, unmet_connectivity_edges. Schema and small sample data you can assume:
users
user_id | country | signup_date
1 | US | 2025-08-15
2 | US | 2025-08-16
3 | US | 2025-08-17
4 | US | 2025-08-18
5 | US | 2025-08-19
6 | US | 2025-08-20
calls
call_id | caller_id | callee_id | start_ts | end_ts | connected
101 | 1 | 2 | 2025-08-31 09:00:00 | 2025-08-31 09:04:00 | 1
102 | 1 | 3 | 2025-08-31 09:05:00 | 2025-08-31 09:08:00 | 1
103 | 2 | 3 | 2025-08-31 09:06:00 | 2025-08-31 09:07:00 | 0
104 | 3 | 1 | 2025-08-31 09:09:00 | 2025-08-31 09:12:00 | 1
105 | 4 | 5 | 2025-08-31 21:00:00 | 2025-08-31 21:03:00 | 1
106 | 4 | 6 | 2025-08-31 21:04:00 | 2025-08-31 21:07:00 | 1
107 | 5 | 6 | 2025-08-31 21:06:00 | 2025-08-31 21:08:00 | 1
108 | 5 | 4 | 2025-08-31 21:09:00 | 2025-08-31 21:15:00 | 0
Overview: This question evaluates SQL-based data manipulation and product-analytics competencies, including temporal sessionization, graph connectivity inference for detecting overlapping 1:1 call loops, deduplication trade-offs (UNION vs UNION ALL), and computation of derived metrics to estimate latent feature demand within the Data Manipulation (SQL/Python) domain. It is commonly asked because it measures practical application of event-time windowing, connected-component reasoning and edge-case handling (e.g., excluding failed connections) for producing daily summary metrics, emphasizing hands-on SQL proficiency over purely conceptual understanding.
Read the full Meta Data Scientist interview experience this question came from
Build undirected 1:1 call edges
Using the users and calls tables, build an undirected edge view of 1:1 calls between 2025-05-26 and 2025-06-01 (inclusive). Treat caller_id and callee_id symmetrically so that each successful call with connected = 1 appears once as (user_a, user_b) with user_a < user_b. Return call_id, user_a, user_b, start_ts, end_ts for all such calls in the date range, formatting the two timestamp columns as `YYYY-MM-DD HH24:MI:SS`. In your solution, be ready to explain when UNION vs UNION ALL is appropriate if you generate symmetric edges, and how premature deduplication can hide repeated calls between the same pair of users.
Tables
users(user_id INT, country VARCHAR(2), signup_date DATE)
calls(call_id INT, caller_id INT, callee_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, connected INT)
Hints
- Use `LEAST(caller_id, callee_id)` and `GREATEST(caller_id, callee_id)` to build the undirected pair.
- Keep `call_id` so repeated calls between the same users remain separate rows.
Sessionize successful calls into 10-minute loop sessions
## Sessionize successful calls into 10-minute loop sessions
You are given two tables, `users` and `calls`. Each row in `calls` is one call attempt with a `caller_id`, a `callee_id`, a `start_ts`, an `end_ts`, and a `connected` flag (`1` = the call connected successfully, `0` = it did not).
Consider **only successful calls** (`connected = 1`) whose `start_ts` falls between **2025-05-26 00:00:00 and 2025-06-01 23:59:59 inclusive** (i.e. the 7 days from 2025-05-26 through 2025-06-01).
Sessionize these calls into **loop sessions** using a rolling 10-minute gap rule:
1. Order the successful calls by `start_ts` (break ties by `call_id`).
2. The first call starts a new session.
3. For every subsequent call, compare its `start_ts` to the **immediately preceding call's** `end_ts`. If `start_ts <= previous_call.end_ts + 10 minutes`, the call belongs to the **same session** as the previous call. Otherwise it **starts a new session**.
4. Use the `call_id` of the first call in a session as that session's `session_id`.
You must perform the sessionization with a **recursive CTE** (walk the ordered calls one at a time).
For each session, produce one row with:
- `event_date` — the `DATE` of the session's earliest call's `start_ts`
- `session_id` — the `call_id` of the first call in the session
- `session_start_ts` — the minimum `start_ts` across the session's calls
- `session_end_ts` — the maximum `end_ts` across the session's calls
- `loop_user_count` — the number of **distinct** user ids appearing across both `caller_id` and `callee_id` of the session's calls
**Return only sessions where `loop_user_count >= 3`.** Order the result by `event_date`, then by `session_id` (both ascending).
Tables
users(user_id INT, country VARCHAR(2), signup_date DATE)
calls(call_id INT, caller_id INT, callee_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, connected INT)
Hints
- The CTE references itself (`sessions` appears inside its own definition), so the whole `WITH` must be declared `WITH RECURSIVE` — without it PostgreSQL cannot resolve the self-reference.
- Use ROW_NUMBER() OVER (ORDER BY start_ts, call_id) so the recursive step can join each call to its immediate predecessor via `rn = prev.rn + 1`.
Daily unmet connectivity from loop sessions
You are given two tables, `users` and `calls`. A **call** has a `caller_id`, a `callee_id`, a `start_ts`, an `end_ts`, and a `connected` flag (1 = the call connected, 0 = it did not).
First restrict attention to **successful calls only** (`connected = 1`) whose `start_ts` falls in the window **2025-05-26 (inclusive) through 2025-06-01 (inclusive)**.
A **loop session** is built by gap-based sessionization over those successful calls:
- Order the successful calls by `start_ts` (break ties by `call_id`).
- A call starts a **new** session whenever it is the first call, or its `start_ts` is **more than 10 minutes after the `end_ts` of the immediately preceding call**; otherwise it continues the current session.
For each session, the **session's users** are all distinct `caller_id` and `callee_id` values appearing on its calls, and the **session's date** is the calendar date (`::date`) of the session's earliest `start_ts`.
For every `event_date`, compute the following three metrics, considering only sessions that have **at least 3 distinct users** (call these *qualifying sessions*):
1. **loop_sessions** — the number of qualifying sessions on that date.
2. **loop_users** — the number of distinct users who appear in at least one qualifying session on that date.
3. **unmet_connectivity_edges** — for each qualifying session let `n` be its number of distinct users and `observed_unique_pairs` be the number of distinct **undirected** user pairs (canonicalize each caller/callee pair so direction is ignored) that actually had a successful call in that session. Define `unmet_per_session = n * (n - 1) / 2 - observed_unique_pairs`, and sum this over all qualifying sessions on that date.
Return one row per `event_date` with columns **`event_date`, `loop_sessions`, `loop_users`, `unmet_connectivity_edges`**, ordered by `event_date` ascending.
Tables
users(user_id INT, country VARCHAR(2), signup_date DATE)
calls(call_id INT, caller_id INT, callee_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, connected INT)
Hints
- Gap-based sessionization: flag a new session when LAG(end_ts) is NULL or start_ts is more than 10 minutes past the previous call's end_ts, then take a running SUM of that flag as the session id. Use WITH RECURSIVE only if you really walk row-by-row.
- Canonicalize each caller/callee pair with LEAST/GREATEST and COUNT(DISTINCT ...) to get observed_unique_pairs; the maximum possible pairs is n*(n-1)/2.
Incorporate failed call attempts into unmet connectivity
You are analyzing call-connectivity data in the `calls` table. Each row is one call attempt with `call_id`, `caller_id`, `callee_id`, `start_ts`, `end_ts`, and `connected` (1 = the call connected successfully, 0 = the attempt failed).
First reconstruct **loop sessions** exactly as in Question 2, using **only successful calls** (`connected = 1`) whose `start_ts` falls in the date range **2025-05-26 to 2025-06-01** (inclusive):
1. Order the successful calls by `start_ts`, then `call_id`.
2. Sessionize them with a **10-minute gap rule**: a call belongs to the current session if its `start_ts` is no later than the running maximum `end_ts` of the session so far plus 10 minutes; otherwise it starts a new session.
3. A session is a **loop session** if it involves **at least 3 distinct users** (counting both callers and callees). For each loop session, its time window runs from `session_start_ts` (the minimum `start_ts`) to `session_end_ts` (the maximum `end_ts`).
Now run a sensitivity analysis that, **per `event_date`**, counts how many additional **unordered user pairs** attempted to communicate but never successfully connected inside any loop session that day. A pair `(user_a, user_b)` (with `user_a < user_b`) is counted **once** for a loop session's date if **both** hold:
- (a) there is at least one **failed** call (`connected = 0`) between the two users whose `start_ts` falls within that loop session's window (`session_start_ts` to `session_end_ts`, inclusive); **and**
- (b) there is **no successful** call (`connected = 1`) between that same unordered pair inside that loop session.
Define `event_date` as the date of the loop session's `session_start_ts`.
**Return** two columns:
- `event_date` — the day of the loop session.
- `failed_only_pairs` — the number of distinct unordered pairs satisfying (a) and (b) on that day.
Return one row per day, **ordered by `event_date` ascending**.
Tables
users(user_id INT, country VARCHAR(2), signup_date DATE)
calls(call_id INT, caller_id INT, callee_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP, connected INT)
Hints
- Sessionize the successful calls with a gaps-and-islands pattern: compute the running MAX(end_ts) of earlier calls with a window frame `ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING`, flag a new session on a >10-minute gap, then SUM the flags to get a session id (no recursive CTE needed).
- Keep only sessions with at least 3 distinct participants (UNION the caller and callee columns), then take each session's MIN(start_ts) and MAX(end_ts) as its window.