Coding questions · Twitch

Write robust SQL for streaming analytics

This question evaluates a candidate's ability to author robust SQL for streaming analytics, covering de-duplication, event-time handling, sessionization, concurrency, overlap detection, and retention calculations on time-series view event data.

Write efficient PostgreSQL SQL for the following streaming analytics tasks. Use the invented schema and sample data below. Handle duplicates, out-of-order events, and missing stops as specified.

Schema:

streams(stream_id INT PRIMARY KEY, creator_id INT, game_id INT, started_at TIMESTAMP, ended_at TIMESTAMP, is_partner BOOLEAN)
view_events(user_id INT, stream_id INT, ts TIMESTAMP, action TEXT CHECK (action IN ('start','stop')), device TEXT, country TEXT)

Sample data (streams):

+-----------+------------+---------+---------------------+---------------------+------------+
| stream_id | creator_id | game_id | started_at | ended_at | is_partner |
+-----------+------------+---------+---------------------+---------------------+------------+
| 101 | 1 | 10 | 2025-08-31 23:50:00 | 2025-09-01 02:10:00 | true |
| 102 | 2 | 11 | 2025-09-01 00:05:00 | 2025-09-01 01:00:00 | false |
| 103 | 1 | 12 | 2025-09-01 03:00:00 | 2025-09-01 04:00:00 | true |
+-----------+------------+---------+---------------------+---------------------+------------+

Sample data (view_events):

+---------+-----------+---------------------+--------+--------+---------+
| user_id | stream_id | ts | action | device | country |
+---------+-----------+---------------------+--------+--------+---------+
| 1001 | 101 | 2025-08-31 23:55:00 | start | mobile | US |
| 1001 | 101 | 2025-09-01 00:40:00 | stop | mobile | US |
| 1002 | 101 | 2025-09-01 00:00:00 | start | web | US |
| 1002 | 101 | 2025-09-01 00:20:00 | stop | web | US |
| 1001 | 102 | 2025-09-01 00:10:00 | start | mobile | US |
| 1001 | 102 | 2025-09-01 00:45:00 | stop | mobile | US |
| 1003 | 102 | 2025-09-01 00:20:00 | start | tv | CA |
| 1003 | 102 | 2025-09-01 00:50:00 | stop | tv | CA |
| 1002 | 103 | 2025-09-01 03:05:00 | start | web | US |
| 1002 | 103 | 2025-09-01 03:45:00 | stop | web | US |
+---------+-----------+---------------------+--------+--------+---------+

Assumptions to enforce in your SQL:

Events can arrive out of order; de-duplicate exact duplicate rows.
If a 'start' has no subsequent 'stop', treat its stop as LEAST(streams.ended_at, ts + interval '4 hours').
Ignore any 'stop' that occurs before its last unmatched 'start' for the same user_id, stream_id.

Tasks: A) Peak concurrency per stream: return stream_id, peak_concurrent_viewers, and the ts window where the peak occurs. B) Multi-streaming overlaps on 2025-09-01: return user_id and the total minutes they watched two different streams concurrently for >= 5 minutes overlap windows. C) 7-day new-viewer retention: considering users whose first-ever view occurred between 2025-08-24 and 2025-08-31, compute D+7 retention where a user is retained if they watch any stream for at least 2 minutes on their 7th day after first view. Assume today is 2025-09-01. D) Top creators by average per-viewer watch time in the last 7 days (2025-08-26 to 2025-09-01): return creator_id and avg_watch_minutes among US viewers, restricted to creators with >= 100 unique US viewers; break ties by higher peak concurrency from Task A.

Peak concurrent viewers per stream

Using the streams and view_events tables below, write an efficient PostgreSQL query to compute peak viewer concurrency per stream.

Assumptions you must enforce in SQL:

Events can arrive out of order; first de-duplicate exact duplicate rows in view_events.
For each (user_id, stream_id), pair start/stop events in timestamp order. Ignore any stop that occurs before its last unmatched start (i.e., stops that would close a non-existent session).
If a start has no subsequent stop, treat its stop as LEAST(streams.ended_at, start_ts + interval '4 hours').
Clamp all viewing sessions to lie within their stream's [started_at, ended_at] interval.

Task: Compute, for every stream_id, the maximum number of concurrent viewers (peak_concurrent_viewers) and a representative time window [peak_start_ts, peak_end_ts) during which this peak occurs. If there are multiple such windows, return the earliest one.

Render peak_start_ts and peak_end_ts as YYYY-MM-DD HH24:MI:SS.

Table `streams`

column	type	constraints
stream_id	INT	PRIMARY KEY, NOT NULL
creator_id	INT	NOT NULL
game_id	INT	NOT NULL
started_at	TIMESTAMP	NOT NULL
ended_at	TIMESTAMP	NOT NULL
is_partner	BOOLEAN	NOT NULL

Sample data:

creator_id	ended_at	game_id	is_partner	started_at	stream_id
1	2025-06-01 02:10:00	10	true	2025-05-31 23:50:00	101
2	2025-06-01 01:00:00	11	false	2025-06-01 00:05:00	102
1	2025-06-01 04:00:00	12	true	2025-06-01 03:00:00	103
3	2025-05-24 11:00:00	13	false	2025-05-24 10:00:00	104
1	2025-05-25 10:30:00	10	true	2025-05-25 09:00:00	105
2	2025-05-31 23:30:00	11	false	2025-05-31 22:00:00	106

Table `view_events`

column	type	constraints
user_id	INT	NOT NULL
stream_id	INT	NOT NULL
ts	TIMESTAMP	NOT NULL
action	TEXT	NOT NULL, CHECK (action IN ('start','stop'))
device	TEXT	NOT NULL
country	TEXT	NOT NULL

Sample data:

action	country	device	stream_id	ts	user_id
start	US	web	104	2025-05-24 10:05:00	2001
stop	US	web	104	2025-05-24 10:35:00	2001
start	CA	mobile	104	2025-05-24 10:20:00	2003
stop	CA	mobile	104	2025-05-24 10:25:00	2003
start	US	mobile	105	2025-05-25 09:10:00	2002
stop	US	mobile	105	2025-05-25 09:25:00	2002
start	US	web	106	2025-05-31 22:05:00	2001
stop	US	web	106	2025-05-31 22:20:00	2001
start	US	mobile	101	2025-05-31 23:55:00	1001
stop	US	mobile	101	2025-06-01 00:40:00	1001
start	US	web	101	2025-06-01 00:00:00	1002
stop	US	web	101	2025-06-01 00:20:00	1002
start	US	mobile	102	2025-06-01 00:10:00	1001
stop	US	mobile	102	2025-06-01 00:45:00	1001
start	CA	tv	102	2025-06-01 00:20:00	1003
stop	CA	tv	102	2025-06-01 00:50:00	1003
start	US	web	103	2025-06-01 03:05:00	1002
stop	US	web	103	2025-06-01 03:45:00	1002
start	US	web	103	2025-06-01 03:05:00	1002
stop	US	mobile	103	2025-06-01 03:05:00	2004
start	US	mobile	103	2025-06-01 03:10:00	2004
stop	US	mobile	103	2025-06-01 03:25:00	2004
start	US	web	103	2025-06-01 03:50:00	2005
start	US	mobile	103	2025-06-01 03:10:00	2002
stop	US	mobile	103	2025-06-01 03:11:00	2002

Expected output

For each stream_id, return the maximum number of concurrent viewers and one earliest interval [peak_start_ts, peak_end_ts) where that concurrency is achieved, after de-duplicating events and building robust viewing sessions.

peak_concurrent_viewers	peak_end_ts	peak_start_ts	stream_id
2	2025-06-01 00:20:00	2025-06-01 00:00:00	101
2	2025-06-01 00:45:00	2025-06-01 00:20:00	102
3	2025-06-01 03:11:00	2025-06-01 03:10:00	103
2	2025-05-24 10:25:00	2025-05-24 10:20:00	104
1	2025-05-25 09:25:00	2025-05-25 09:10:00	105
1	2025-05-31 22:20:00	2025-05-31 22:05:00	106

Hints

Convert cleaned start/stop events into viewing sessions before computing concurrency.
Use +1 start edges and -1 end edges with a running sum to find concurrent viewers over time.
After finding the peak interval, format the start and end timestamps with PostgreSQL TO_CHAR.

Multi-streaming overlap on a specific day

You are analyzing Twitch viewing behavior using two tables:

streams — one row per stream, with stream_id, creator_id, game_id, started_at, ended_at, is_partner.
view_events — raw watch events, with user_id, stream_id, ts, action ('start' or 'stop'), device, country.

Building robust viewing sessions

For each (user_id, stream_id), pair up 'start' / 'stop' events into sessions, applying these robustness rules (the same rules used in Task A):

De-duplicate exact duplicate events — collapse rows that are identical on (user_id, stream_id, ts, action).
Ignore invalid stops — a 'stop' that has no currently-open 'start' before it (an unmatched stop) is discarded. Process events in time order, using 'start' before 'stop' when two events share the same timestamp.
Fill missing stops — if a 'start' has no matching 'stop', treat the session end as LEAST(stream.ended_at, start_ts + interval '4 hours').
Clamp to the stream window — every session is clamped so that it starts no earlier than stream.started_at and ends no later than stream.ended_at. Keep a session only if, after clamping, its end is strictly after its start.

The task

Using those robust sessions, compute multi-streaming overlap per user on the calendar day 2025-06-01:

Consider only the portion of each session that falls within the day window ['2025-06-01 00:00:00', '2025-06-02 00:00:00') — clamp each session to that window before measuring overlap.
For each user_id, look at every pair of that user's sessions on two different streams and compute the overlapping time window (where both sessions are active simultaneously).
Count an overlap only if it lasts at least 5 minutes. For each qualifying pair, add its overlap duration (in minutes) to that user's total.

Required output

Return one row per user_id that has at least one qualifying concurrent-watch overlap, with columns:

user_id
total_overlap_minutes — the sum of all qualifying (>= 5 minute) overlap durations in minutes for that user on 2025-06-01.

Order the result by user_id ascending. With the provided sample data, only user 1001 multi-streams on that day.

Table `streams`

column	type	constraints
stream_id	INT	PRIMARY KEY, NOT NULL
creator_id	INT	NOT NULL
game_id	INT	NOT NULL
started_at	TIMESTAMP	NOT NULL
ended_at	TIMESTAMP	NOT NULL
is_partner	BOOLEAN	NOT NULL

Sample data:

creator_id	ended_at	game_id	is_partner	started_at	stream_id
1	2025-06-01 02:10:00	10	true	2025-05-31 23:50:00	101
2	2025-06-01 01:00:00	11	false	2025-06-01 00:05:00	102
1	2025-06-01 04:00:00	12	true	2025-06-01 03:00:00	103
3	2025-05-24 11:00:00	13	false	2025-05-24 10:00:00	104
1	2025-05-25 10:30:00	10	true	2025-05-25 09:00:00	105
2	2025-05-31 23:30:00	11	false	2025-05-31 22:00:00	106

Table `view_events`

column	type	constraints
user_id	INT	NOT NULL
stream_id	INT	NOT NULL
ts	TIMESTAMP	NOT NULL
action	TEXT	NOT NULL, CHECK (action IN ('start','stop'))
device	TEXT	NOT NULL
country	TEXT	NOT NULL

Sample data:

action	country	device	stream_id	ts	user_id
start	US	web	104	2025-05-24 10:05:00	2001
stop	US	web	104	2025-05-24 10:35:00	2001
start	CA	mobile	104	2025-05-24 10:20:00	2003
stop	CA	mobile	104	2025-05-24 10:25:00	2003
start	US	mobile	105	2025-05-25 09:10:00	2002
stop	US	mobile	105	2025-05-25 09:25:00	2002
start	US	web	106	2025-05-31 22:05:00	2001
stop	US	web	106	2025-05-31 22:20:00	2001
start	US	mobile	101	2025-05-31 23:55:00	1001
stop	US	mobile	101	2025-06-01 00:40:00	1001
start	US	web	101	2025-06-01 00:00:00	1002
stop	US	web	101	2025-06-01 00:20:00	1002
start	US	mobile	102	2025-06-01 00:10:00	1001
stop	US	mobile	102	2025-06-01 00:45:00	1001
start	CA	tv	102	2025-06-01 00:20:00	1003
stop	CA	tv	102	2025-06-01 00:50:00	1003
start	US	web	103	2025-06-01 03:05:00	1002
stop	US	web	103	2025-06-01 03:45:00	1002
start	US	web	103	2025-06-01 03:05:00	1002
stop	US	mobile	103	2025-06-01 03:05:00	2004
start	US	mobile	103	2025-06-01 03:10:00	2004
stop	US	mobile	103	2025-06-01 03:25:00	2004
start	US	web	103	2025-06-01 03:50:00	2005
start	US	mobile	103	2025-06-01 03:10:00	2002
stop	US	mobile	103	2025-06-01 03:11:00	2002

Expected output

One row per user_id with the total minutes on 2025-06-01 they watched two different streams concurrently, counting only overlapping windows of at least 5 minutes. With the sample data only user 1001 qualifies (30 minutes).

total_overlap_minutes	user_id
30.0000000000000000	1001

Hints

Build robust sessions first: de-duplicate events, use a running balance of starts/stops to drop unmatched stops, then pair each start with its matching stop via a session index.
Clamp each session to BOTH the stream window and the day window [2025-06-01, 2025-06-02) before measuring overlap.
Self-join a user's day-sessions on a.stream_id < b.stream_id, take GREATEST(starts) and LEAST(ends) for the overlap window, keep only overlaps >= 5 minutes, and sum per user.

7-day new-viewer retention

Using the same streams and view_events tables and robust session rules (de-duplicate events, ignore invalid stops before an unmatched start, fill missing stops with LEAST(ended_at, start_ts + interval '4 hours'), clamp sessions to stream windows), compute D+7 new-viewer retention.

Assume "today" is 2025-06-01. Consider users whose first-ever view (their earliest session_start) occurred between 2025-05-24 and 2025-05-25 (inclusive). For each such cohort day (first_view_date):

Define the D+7 date as first_view_date + 7 days.
A user is counted as retained if, on that D+7 calendar day, they watch any stream for at least 2 minutes in total.

Return one row per first_view_date with: first_view_date, cohort_size (number of new users that day), retained_users, and retention_rate = retained_users / cohort_size.

With the sample data:

Users 2001 and 2003 have first views on 2025-05-24; only 2001 watches ≥2 minutes on 2025-05-31 (their D+7), so retention is 1/2.
User 2002 has first view on 2025-05-25 and watches only 1 minute on 2025-06-01 (their D+7), so retention is 0/1.

Table `streams`

column	type	constraints
stream_id	INT	PRIMARY KEY, NOT NULL
creator_id	INT	NOT NULL
game_id	INT	NOT NULL
started_at	TIMESTAMP	NOT NULL
ended_at	TIMESTAMP	NOT NULL
is_partner	BOOLEAN	NOT NULL

Sample data:

creator_id	ended_at	game_id	is_partner	started_at	stream_id
1	2025-06-01 02:10:00	10	true	2025-05-31 23:50:00	101
2	2025-06-01 01:00:00	11	false	2025-06-01 00:05:00	102
1	2025-06-01 04:00:00	12	true	2025-06-01 03:00:00	103
3	2025-05-24 11:00:00	13	false	2025-05-24 10:00:00	104
1	2025-05-25 10:30:00	10	true	2025-05-25 09:00:00	105
2	2025-05-31 23:30:00	11	false	2025-05-31 22:00:00	106

Table `view_events`

column	type	constraints
user_id	INT	NOT NULL
stream_id	INT	NOT NULL
ts	TIMESTAMP	NOT NULL
action	TEXT	NOT NULL, CHECK (action IN ('start','stop'))
device	TEXT	NOT NULL
country	TEXT	NOT NULL

Sample data:

action	country	device	stream_id	ts	user_id
start	US	web	104	2025-05-24 10:05:00	2001
stop	US	web	104	2025-05-24 10:35:00	2001
start	CA	mobile	104	2025-05-24 10:20:00	2003
stop	CA	mobile	104	2025-05-24 10:25:00	2003
start	US	mobile	105	2025-05-25 09:10:00	2002
stop	US	mobile	105	2025-05-25 09:25:00	2002
start	US	web	106	2025-05-31 22:05:00	2001
stop	US	web	106	2025-05-31 22:20:00	2001
start	US	mobile	101	2025-05-31 23:55:00	1001
stop	US	mobile	101	2025-06-01 00:40:00	1001
start	US	web	101	2025-06-01 00:00:00	1002
stop	US	web	101	2025-06-01 00:20:00	1002
start	US	mobile	102	2025-06-01 00:10:00	1001
stop	US	mobile	102	2025-06-01 00:45:00	1001
start	CA	tv	102	2025-06-01 00:20:00	1003
stop	CA	tv	102	2025-06-01 00:50:00	1003
start	US	web	103	2025-06-01 03:05:00	1002
stop	US	web	103	2025-06-01 03:45:00	1002
start	US	web	103	2025-06-01 03:05:00	1002
stop	US	mobile	103	2025-06-01 03:05:00	2004
start	US	mobile	103	2025-06-01 03:10:00	2004
stop	US	mobile	103	2025-06-01 03:25:00	2004
start	US	web	103	2025-06-01 03:50:00	2005
start	US	mobile	103	2025-06-01 03:10:00	2002
stop	US	mobile	103	2025-06-01 03:11:00	2002

Expected output

For each cohort day (users whose first-ever view falls on that date between 2025-05-24 and 2025-05-25), returns the cohort size, how many are retained on D+7 with at least 2 minutes of watch time, and the retention rate.

cohort_size	first_view_date	retained_users	retention_rate
2	2025-05-24	1	0.5
1	2025-05-25	0	0

Hints

Use the sessionized data to find each user's first-ever viewing date, then restrict to the 2025-05-24–2025-05-25 cohort.
For each cohort user, compute watch time that falls exactly on first_view_date + 7 days, flag retained if it is at least 2 minutes, then aggregate by cohort day.

Top creators by per-viewer watch time (last 7 days)

Using the same robust session rules (de-duplicate events, ignore invalid stops before an unmatched start, fill missing stops with LEAST(ended_at, start_ts + interval '4 hours'), clamp to stream windows), compute top creators by average per-viewer watch time among US viewers in the last 7 days relative to 2025-06-01.

Treat the last 7 days as the window from 2025-05-26 00:00:00 through 2025-06-01 23:59:59 (i.e., [2025-05-26, 2025-06-02) ). Within this window:

Consider only sessions where country = 'US'.
For each creator_id and user_id, sum that user's total watch minutes for that creator across all their streams in the window.
For each creator_id, compute avg_watch_minutes = average of total watch minutes per US viewer.
Restrict to creators with at least 2 unique US viewers in this window (small threshold for the sample; in production this might be 100+ viewers).
When ordering creators, break ties in avg_watch_minutes by the creator's highest per-stream peak concurrency (from Task A), using higher peak concurrency first.

Return creator_id and avg_watch_minutes, ordered by avg_watch_minutes descending, then by peak concurrency descending. With the sample data, creators 1 and 2 both qualify.

Table `streams`

column	type	constraints
stream_id	INT	PRIMARY KEY, NOT NULL
creator_id	INT	NOT NULL
game_id	INT	NOT NULL
started_at	TIMESTAMP	NOT NULL
ended_at	TIMESTAMP	NOT NULL
is_partner	BOOLEAN	NOT NULL

Sample data:

creator_id	ended_at	game_id	is_partner	started_at	stream_id
1	2025-06-01 02:10:00	10	true	2025-05-31 23:50:00	101
2	2025-06-01 01:00:00	11	false	2025-06-01 00:05:00	102
1	2025-06-01 04:00:00	12	true	2025-06-01 03:00:00	103
3	2025-05-24 11:00:00	13	false	2025-05-24 10:00:00	104
1	2025-05-25 10:30:00	10	true	2025-05-25 09:00:00	105
2	2025-05-31 23:30:00	11	false	2025-05-31 22:00:00	106

Table `view_events`

column	type	constraints
user_id	INT	NOT NULL
stream_id	INT	NOT NULL
ts	TIMESTAMP	NOT NULL
action	TEXT	NOT NULL, CHECK (action IN ('start','stop'))
device	TEXT	NOT NULL
country	TEXT	NOT NULL

Sample data:

action	country	device	stream_id	ts	user_id
start	US	web	104	2025-05-24 10:05:00	2001
stop	US	web	104	2025-05-24 10:35:00	2001
start	CA	mobile	104	2025-05-24 10:20:00	2003
stop	CA	mobile	104	2025-05-24 10:25:00	2003
start	US	mobile	105	2025-05-25 09:10:00	2002
stop	US	mobile	105	2025-05-25 09:25:00	2002
start	US	web	106	2025-05-31 22:05:00	2001
stop	US	web	106	2025-05-31 22:20:00	2001
start	US	mobile	101	2025-05-31 23:55:00	1001
stop	US	mobile	101	2025-06-01 00:40:00	1001
start	US	web	101	2025-06-01 00:00:00	1002
stop	US	web	101	2025-06-01 00:20:00	1002
start	US	mobile	102	2025-06-01 00:10:00	1001
stop	US	mobile	102	2025-06-01 00:45:00	1001
start	CA	tv	102	2025-06-01 00:20:00	1003
stop	CA	tv	102	2025-06-01 00:50:00	1003
start	US	web	103	2025-06-01 03:05:00	1002
stop	US	web	103	2025-06-01 03:45:00	1002
start	US	web	103	2025-06-01 03:05:00	1002
stop	US	mobile	103	2025-06-01 03:05:00	2004
start	US	mobile	103	2025-06-01 03:10:00	2004
stop	US	mobile	103	2025-06-01 03:25:00	2004
start	US	web	103	2025-06-01 03:50:00	2005
start	US	mobile	103	2025-06-01 03:10:00	2002
stop	US	mobile	103	2025-06-01 03:11:00	2002

Expected output

Creators ranked by average per-US-viewer watch time in minutes over the 2025-05-26–2025-06-01 window, restricted to creators with at least 2 unique US viewers, with ties broken by higher peak concurrency from Task A. In the sample, creator 1 has slightly higher average watch time than creator 2.

avg_watch_minutes	creator_id
26.2	1
25	2

Hints

From the sessionized data, restrict to US sessions in the 2025-05-26–2025-06-01 window, then aggregate minutes per (creator_id, user_id) before averaging per creator.
Reuse the peak-concurrency logic from Task A to derive each creator's maximum stream peak and use it as a secondary ORDER BY tie-breaker after avg_watch_minutes.

Peak concurrent viewers per stream

Table streams

Table view_events

Expected output

Hints

Multi-streaming overlap on a specific day

Building robust viewing sessions

The task

Required output

Table streams

Table view_events

Expected output

Hints

7-day new-viewer retention

Table streams

Table view_events

Expected output

Hints

Top creators by per-viewer watch time (last 7 days)

Table streams

Table view_events

Expected output

Hints

Table `streams`

Table `view_events`

Table `streams`

Table `view_events`

Table `streams`

Table `view_events`

Table `streams`

Table `view_events`