Quick Overview

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 robust SQL for streaming analytics

Company: Twitch

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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.

Overview: 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.

Read the full Twitch Data Scientist interview experience this question came from

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

Tables

streams(stream_id INT, 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, device TEXT, country TEXT)

Hints

  1. Convert cleaned start/stop events into viewing sessions before computing concurrency.
  2. Use +1 start edges and -1 end edges with a running sum to find concurrent viewers over time.

Multi-streaming overlap on a specific day

## 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): 1. **De-duplicate exact duplicate events** — collapse rows that are identical on `(user_id, stream_id, ts, action)`. 2. **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. 3. **Fill missing stops** — if a `'start'` has no matching `'stop'`, treat the session end as `LEAST(stream.ended_at, start_ts + interval '4 hours')`. 4. **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.

Tables

streams(stream_id INT, 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, device TEXT, country TEXT)

Hints

  1. 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.
  2. Clamp each session to BOTH the stream window and the day window [2025-06-01, 2025-06-02) before measuring overlap.

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.

Tables

streams(stream_id INT, 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, device TEXT, country TEXT)

Hints

  1. Use the sessionized data to find each user's first-ever viewing date, then restrict to the 2025-05-24–2025-05-25 cohort.
  2. 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.

Tables

streams(stream_id INT, 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, device TEXT, country TEXT)

Hints

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

Loading coding console...