Table: Sessions
+---------------+----------+ | Column Name | Type | +---------------+----------+ | user_id | int | | session_start | datetime | | session_end | datetime | | session_id | int | | session_type | enum | +---------------+----------+ session_id is column of unique values for this table. session_type is an ENUM (category) type of (Viewer, Streamer). This table contains user id, session start, session end, session id and session type.
Write a solution to find the number of streaming sessions for users whose first session was as a viewer.
Return the result table ordered by count of streaming sessions, user_id in descending order.
The result format is in the following example.
Example 1:
Input: Sessions table: +---------+---------------------+---------------------+------------+--------------+ | user_id | session_start | session_end | session_id | session_type | +---------+---------------------+---------------------+------------+--------------+ | 101 | 2023-11-06 13:53:42 | 2023-11-06 14:05:42 | 375 | Viewer | | 101 | 2023-11-22 16:45:21 | 2023-11-22 20:39:21 | 594 | Streamer | | 102 | 2023-11-16 13:23:09 | 2023-11-16 16:10:09 | 777 | Streamer | | 102 | 2023-11-17 13:23:09 | 2023-11-17 16:10:09 | 778 | Streamer | | 101 | 2023-11-20 07:16:06 | 2023-11-20 08:33:06 | 315 | Streamer | | 104 | 2023-11-27 03:10:49 | 2023-11-27 03:30:49 | 797 | Viewer | | 103 | 2023-11-27 03:10:49 | 2023-11-27 03:30:49 | 798 | Streamer | +---------+---------------------+---------------------+------------+--------------+ Output: +---------+----------------+ | user_id | sessions_count | +---------+----------------+ | 101 | 2 | +---------+----------------+ Explanation - user_id 101, initiated their initial session as a viewer on 2023-11-06 at 13:53:42, followed by two subsequent sessions as a Streamer, the count will be 2. - user_id 102, although there are two sessions, the initial session was as a Streamer, so this user will be excluded. - user_id 103 participated in only one session, which was as a Streamer, hence, it won't be considered. - User_id 104 commenced their first session as a viewer but didn't have any subsequent sessions, therefore, they won't be included in the final count. Output table is ordered by sessions count and user_id in descending order.
Problem Overview: You need to identify users who originally appeared as viewers and later became streamers. The challenge is detecting a role transition in chronological order using SQL while ensuring the viewer activity happened before the streaming activity.
Approach 1: Self Join on Activity History (O(n²) time, O(1) extra space)
A straightforward solution compares rows from the same activity table using a self join. One side represents viewer activity and the other represents streaming activity. You match rows by user_id and enforce a condition such as viewer_time < streamer_time. If a user has at least one earlier viewer record and a later streaming record, they qualify as a "viewer turned streamer".
This approach works conceptually but scales poorly. The join potentially compares many rows per user, which can explode to quadratic complexity on large datasets. It also requires careful filtering to avoid duplicate matches when multiple viewer or streamer events exist.
Approach 2: Window Function + Equi-Join (O(n log n) time, O(n) space)
The efficient approach uses a window function to analyze activity order per user. Partition the dataset by user_id and order events by timestamp. With functions like LAG(), you can look at the previous role for the same user and detect when the role changes from viewer to streamer.
Once transitions are identified, an equi-join or filtering step extracts users where the current role is streamer and the previous role was viewer. Window functions handle ordering internally, which avoids repeated joins across the entire dataset. This reduces the logic to a single pass per user after sorting.
This pattern is common in database interview problems where you must track state transitions across time. Window functions allow you to reason about "previous" or "next" rows without complex joins.
Recommended for interviews: The window function approach is the expected solution. A brute self‑join shows you understand relational comparisons, but window functions demonstrate stronger SQL skills and cleaner reasoning about chronological state transitions.
We can use the window function RANK() to rank each session by user_id dimension, and record it in table T. Then, we equi-join T and the Sessions table by user_id, and filter out the records in T where the rank is 1, and session_type is Viewer, and session_type in the Sessions table is Streamer. Finally, we group by user_id and sum up.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self Join on Viewer and Streamer Records | O(n²) | O(1) | Small datasets or when window functions are unavailable |
| Window Function + Equi-Join | O(n log n) | O(n) | Preferred approach for ordered activity analysis in SQL |
Leetcode HARD 2995 - Viewers Turned Streamers - ROW_NUMBER() Ranking in SQL | Everyday Data Science • Everyday Data Science • 804 views views
Practice Viewers Turned Streamers with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor