Viewers Turned Streamers - Problem
Given a Sessions table that tracks user streaming and viewing activity, find the number of streaming sessions for users whose first session was as a viewer.
The Sessions table contains:
user_id- User identifiersession_start- When the session begansession_end- When the session endedsession_id- Unique session identifiersession_type- Either 'Viewer' or 'Streamer'
Return the result ordered by count of streaming sessions descending, then user_id descending.
Table Schema
Sessions
| Column Name | Type | Description |
|---|---|---|
user_id
|
int | User identifier |
session_start
|
datetime | Session start timestamp |
session_end
|
datetime | Session end timestamp |
session_id
PK
|
int | Unique session identifier |
session_type
|
enum | Either 'Viewer' or 'Streamer' |
Primary Key: session_id
Note: Tracks both viewing and streaming sessions for users
Input & Output
Example 1 — Mixed User Types
Input Table:
| user_id | session_start | session_end | session_id | session_type |
|---|---|---|---|---|
| 123 | 2024-01-01 10:00:00 | 2024-01-01 11:00:00 | 1 | Viewer |
| 123 | 2024-01-02 10:00:00 | 2024-01-02 11:00:00 | 2 | Streamer |
| 123 | 2024-01-03 10:00:00 | 2024-01-03 11:00:00 | 3 | Streamer |
| 456 | 2024-01-01 12:00:00 | 2024-01-01 13:00:00 | 4 | Streamer |
| 456 | 2024-01-02 12:00:00 | 2024-01-02 13:00:00 | 5 | Viewer |
| 789 | 2024-01-01 14:00:00 | 2024-01-01 15:00:00 | 6 | Viewer |
Output:
| user_id | sessions_count |
|---|---|
| 123 | 2 |
💡 Note:
User 123's first session was as a Viewer (2024-01-01), so we count their streaming sessions (2). User 456's first session was as a Streamer, so they're excluded. User 789 started as a Viewer but has no streaming sessions.
Example 2 — Multiple Qualifying Users
Input Table:
| user_id | session_start | session_end | session_id | session_type |
|---|---|---|---|---|
| 100 | 2024-01-01 09:00:00 | 2024-01-01 10:00:00 | 1 | Viewer |
| 100 | 2024-01-02 09:00:00 | 2024-01-02 10:00:00 | 2 | Streamer |
| 200 | 2024-01-01 11:00:00 | 2024-01-01 12:00:00 | 3 | Viewer |
| 200 | 2024-01-02 11:00:00 | 2024-01-02 12:00:00 | 4 | Streamer |
| 200 | 2024-01-03 11:00:00 | 2024-01-03 12:00:00 | 5 | Streamer |
| 200 | 2024-01-04 11:00:00 | 2024-01-04 12:00:00 | 6 | Streamer |
Output:
| user_id | sessions_count |
|---|---|
| 200 | 3 |
| 100 | 1 |
💡 Note:
Both users started as viewers. User 200 has 3 streaming sessions and user 100 has 1. Results are ordered by sessions_count DESC, user_id DESC.
Example 3 — No Qualifying Users
Input Table:
| user_id | session_start | session_end | session_id | session_type |
|---|---|---|---|---|
| 111 | 2024-01-01 10:00:00 | 2024-01-01 11:00:00 | 1 | Streamer |
| 222 | 2024-01-01 12:00:00 | 2024-01-01 13:00:00 | 2 | Streamer |
Output:
| user_id | sessions_count |
|---|
💡 Note:
No users started as viewers - both began as streamers, so no results are returned.
Constraints
-
1 ≤ user_id ≤ 10^6 -
session_idis unique for each row -
session_typeis either'Viewer'or'Streamer' -
session_start < session_endfor all rows
Visualization
Tap to expand
Understanding the Visualization
1
Input
Sessions table with user activity
2
Filter
Find viewer-first users
3
Count
Aggregate streaming sessions
Key Takeaway
🎯 Key Insight: Use window functions to efficiently identify chronological ordering and filter users by their journey progression
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code