Coding questions · Meta

Write SQL to infer group-call demand

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.

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

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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-05-15	1
US	2025-05-16	2
US	2025-05-17	3
US	2025-05-18	4
US	2025-05-19	5
US	2025-05-20	6

Table `calls`

column	type	constraints
call_id	INT	PRIMARY KEY, NOT NULL
caller_id	INT	NOT NULL
callee_id	INT	NOT NULL
start_ts	TIMESTAMP	NOT NULL
end_ts	TIMESTAMP	NOT NULL
connected	INT	NOT NULL

Sample data:

call_id	callee_id	caller_id	connected	end_ts	start_ts
101	2	1	1	2025-05-31 09:04:00	2025-05-31 09:00:00
102	3	1	1	2025-05-31 09:08:00	2025-05-31 09:05:00
103	3	2	0	2025-05-31 09:07:00	2025-05-31 09:06:00
104	1	3	1	2025-05-31 09:12:00	2025-05-31 09:09:00
105	5	4	1	2025-05-31 21:03:00	2025-05-31 21:00:00
106	6	4	1	2025-05-31 21:07:00	2025-05-31 21:04:00
107	6	5	1	2025-05-31 21:08:00	2025-05-31 21:06:00
108	4	5	0	2025-05-31 21:15:00	2025-05-31 21:09:00

Expected output

One row per successful call (connected = 1) in the date range 2025-05-26 to 2025-06-01, with an undirected user pair (user_a < user_b) and the call timestamps.

call_id	end_ts	start_ts	user_a	user_b
101	2025-05-31 09:04:00	2025-05-31 09:00:00	1	2
102	2025-05-31 09:08:00	2025-05-31 09:05:00	1	3
104	2025-05-31 09:12:00	2025-05-31 09:09:00	1	3
105	2025-05-31 21:03:00	2025-05-31 21:00:00	4	5
106	2025-05-31 21:07:00	2025-05-31 21:04:00	4	6
107	2025-05-31 21:08:00	2025-05-31 21:06:00	5	6

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.
Format start_ts and end_ts with PostgreSQL TO_CHAR.

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:

Order the successful calls by start_ts (break ties by call_id).
The first call starts a new session.
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.
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).

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-05-15	1
US	2025-05-16	2
US	2025-05-17	3
US	2025-05-18	4
US	2025-05-19	5
US	2025-05-20	6

Table `calls`

column	type	constraints
call_id	INT	PRIMARY KEY, NOT NULL
caller_id	INT	NOT NULL
callee_id	INT	NOT NULL
start_ts	TIMESTAMP	NOT NULL
end_ts	TIMESTAMP	NOT NULL
connected	INT	NOT NULL

Sample data:

call_id	callee_id	caller_id	connected	end_ts	start_ts
101	2	1	1	2025-05-31 09:04:00	2025-05-31 09:00:00
102	3	1	1	2025-05-31 09:08:00	2025-05-31 09:05:00
103	3	2	0	2025-05-31 09:07:00	2025-05-31 09:06:00
104	1	3	1	2025-05-31 09:12:00	2025-05-31 09:09:00
105	5	4	1	2025-05-31 21:03:00	2025-05-31 21:00:00
106	6	4	1	2025-05-31 21:07:00	2025-05-31 21:04:00
107	6	5	1	2025-05-31 21:08:00	2025-05-31 21:06:00
108	4	5	0	2025-05-31 21:15:00	2025-05-31 21:09:00

Expected output

One row per loop session (a 10-minute-gap-based group of successful calls) with at least 3 distinct users, showing event_date, session_id, the session's time bounds, and the distinct-user count, ordered by event_date then session_id.

event_date	loop_user_count	session_end_ts	session_id	session_start_ts
2025-05-31	3	2025-05-31T09:12:00	101	2025-05-31T09:00:00
2025-05-31	3	2025-05-31T21:08:00	105	2025-05-31T21:00:00

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.
Carry the running session_id forward in the recursive term: keep the previous session's id when start_ts <= prev.end_ts + INTERVAL '10 minutes', otherwise reset it to the current call_id.

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):

loop_sessions — the number of qualifying sessions on that date.
loop_users — the number of distinct users who appear in at least one qualifying session on that date.
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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-05-15	1
US	2025-05-16	2
US	2025-05-17	3
US	2025-05-18	4
US	2025-05-19	5
US	2025-05-20	6

Table `calls`

column	type	constraints
call_id	INT	PRIMARY KEY, NOT NULL
caller_id	INT	NOT NULL
callee_id	INT	NOT NULL
start_ts	TIMESTAMP	NOT NULL
end_ts	TIMESTAMP	NOT NULL
connected	INT	NOT NULL

Sample data:

call_id	callee_id	caller_id	connected	end_ts	start_ts
101	2	1	1	2025-05-31 09:04:00	2025-05-31 09:00:00
102	3	1	1	2025-05-31 09:08:00	2025-05-31 09:05:00
103	3	2	0	2025-05-31 09:07:00	2025-05-31 09:06:00
104	1	3	1	2025-05-31 09:12:00	2025-05-31 09:09:00
105	5	4	1	2025-05-31 21:03:00	2025-05-31 21:00:00
106	6	4	1	2025-05-31 21:07:00	2025-05-31 21:04:00
107	6	5	1	2025-05-31 21:08:00	2025-05-31 21:06:00
108	4	5	0	2025-05-31 21:15:00	2025-05-31 21:09:00

Expected output

One row per event_date with the count of qualifying loop sessions (>= 3 distinct users), the count of distinct users in those sessions, and the total unmet undirected user pairs summed across those sessions.

event_date	loop_sessions	loop_users	unmet_connectivity_edges
2025-05-31	2	6	1

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.
For loop_users, count distinct users across all qualifying sessions on a date (a user in two sessions the same day counts once) — a correlated subquery or a separate aggregation avoids double-counting via the join.

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):

Order the successful calls by start_ts, then call_id.
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.
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.

Table `users`

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
country	VARCHAR(2)	NOT NULL
signup_date	DATE	NOT NULL

Sample data:

country	signup_date	user_id
US	2025-05-15	1
US	2025-05-16	2
US	2025-05-17	3
US	2025-05-18	4
US	2025-05-19	5
US	2025-05-20	6

Table `calls`

column	type	constraints
call_id	INT	PRIMARY KEY, NOT NULL
caller_id	INT	NOT NULL
callee_id	INT	NOT NULL
start_ts	TIMESTAMP	NOT NULL
end_ts	TIMESTAMP	NOT NULL
connected	INT	NOT NULL

Sample data:

call_id	callee_id	caller_id	connected	end_ts	start_ts
101	2	1	1	2025-05-31 09:04:00	2025-05-31 09:00:00
102	3	1	1	2025-05-31 09:08:00	2025-05-31 09:05:00
103	3	2	0	2025-05-31 09:07:00	2025-05-31 09:06:00
104	1	3	1	2025-05-31 09:12:00	2025-05-31 09:09:00
105	5	4	1	2025-05-31 21:03:00	2025-05-31 21:00:00
106	6	4	1	2025-05-31 21:07:00	2025-05-31 21:04:00
107	6	5	1	2025-05-31 21:08:00	2025-05-31 21:06:00
108	4	5	0	2025-05-31 21:15:00	2025-05-31 21:09:00

Expected output

One row per day giving the count of distinct unordered user pairs that had a failed call inside a loop-session window but no successful call between them in that session.

event_date	failed_only_pairs
2025-05-31	1

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.
Normalize every pair with LEAST/GREATEST and use a LEFT JOIN ... IS NULL anti-join to drop failed pairs that also connected successfully in the same session.

Build undirected 1:1 call edges

Table users

Table calls

Expected output

Hints

Sessionize successful calls into 10-minute loop sessions

Table users

Table calls

Expected output

Hints

Daily unmet connectivity from loop sessions

Table users

Table calls

Expected output

Hints

Incorporate failed call attempts into unmet connectivity

Table users

Table calls

Expected output

Hints

Table `users`

Table `calls`

Table `users`

Table `calls`

Table `users`

Table `calls`

Table `users`

Table `calls`