Viewers Turned Streamers - Practice Coding Problems

Viewers Turned Streamers - Problem

Database Hard

Imagine you're analyzing user behavior on a streaming platform where users can either watch streams or create their own content. You have a database table containing all user sessions with their activities.

Your task is to identify users who started as viewers but eventually became streamers. Specifically, you need to:

Find users whose first ever session was as a "Viewer"
Count how many "Streamer" sessions these users had in total
Return results ordered by streaming session count (descending), then by user_id (descending)

The Sessions table contains:

user_id: User identifier
session_start: When the session began
session_end: When the session ended
session_id: Unique session identifier
session_type: Either 'Viewer' or 'Streamer'

This problem helps identify user engagement patterns and potential content creators who evolved from passive consumers to active contributors.

Input & Output

example_1.sql — Basic Example

$ Input: Sessions table: | user_id | session_start | session_end | session_id | session_type | |---------|--------------|-------------|------------|-------------| | 1 | 2023-11-01 | 2023-11-01 | 101 | Viewer | | 1 | 2023-11-02 | 2023-11-02 | 102 | Streamer | | 1 | 2023-11-03 | 2023-11-03 | 103 | Streamer | | 2 | 2023-11-01 | 2023-11-01 | 201 | Streamer | | 2 | 2023-11-02 | 2023-11-02 | 202 | Viewer |

› Output: | user_id | streaming_sessions | |---------|-------------------| | 1 | 2 |

💡 Note: User 1's first session was as a Viewer (2023-11-01), then had 2 Streamer sessions. User 2's first session was as a Streamer, so they're excluded from results.

example_2.sql — Multiple Users

$ Input: Sessions table: | user_id | session_start | session_end | session_id | session_type | |---------|--------------|-------------|------------|-------------| | 3 | 2023-11-01 | 2023-11-01 | 301 | Viewer | | 3 | 2023-11-02 | 2023-11-02 | 302 | Streamer | | 4 | 2023-11-01 | 2023-11-01 | 401 | Viewer | | 5 | 2023-11-01 | 2023-11-01 | 501 | Viewer | | 5 | 2023-11-02 | 2023-11-02 | 502 | Viewer |

› Output: | user_id | streaming_sessions | |---------|-------------------| | 5 | 0 | | 4 | 0 | | 3 | 1 |

💡 Note: Users 3, 4, and 5 all started as viewers. User 3 has 1 streaming session, users 4 and 5 have 0. Results ordered by streaming_sessions DESC, then user_id DESC.

example_3.sql — Edge Case

$ Input: Sessions table: | user_id | session_start | session_end | session_id | session_type | |---------|--------------|-------------|------------|-------------| | 6 | 2023-11-01 | 2023-11-01 | 601 | Viewer | | 7 | 2023-11-01 | 2023-11-01 | 701 | Streamer |

› Output: | user_id | streaming_sessions | |---------|-------------------| | 6 | 0 |

💡 Note: User 6 started as viewer but never streamed (0 streaming sessions). User 7 started as streamer, so excluded. Even users with 0 streaming sessions should be included if they started as viewers.

Constraints

1 ≤ number of sessions ≤ 10⁴
1 ≤ user_id ≤ 10³
session_start < session_end for all sessions
session_type is either 'Viewer' or 'Streamer'
session_id is unique across all sessions
Each user has at least one session

Visualization

Tap to expand

Understanding the Visualization

Identify First Session

Use FIRST_VALUE() to mark each user's chronologically first session type

Filter Viewer-First Users

Keep only users whose journey started with watching content

Count Streaming Activity

Aggregate streaming sessions to measure content creation volume

Rank by Activity

Order by streaming count to highlight most active creator converts

Key Takeaway

🎯 Key Insight: Window functions enable complex user journey analysis in a single query, efficiently identifying behavioral transitions from passive consumption to active content creation.

Asked in

f Meta 45 a Amazon 38 G Google 32 N Netflix 28 ▶ YouTube 25

The optimal approach uses a single window function query with FIRST_VALUE() to determine each user's first session type, then filters for viewer-first users and counts their streaming sessions. This eliminates multiple table scans and provides O(n log n) performance, making it ideal for large datasets with complex user journey analysis.

Common Approaches

Approach	Time	Space	Notes
✓ Single Window Function Query (Optimal)	O(n log n)	O(n)	Use window functions to get first session type and count streaming sessions in one query
Nested Subqueries (Brute Force)	O(n²)	O(n)	Use nested subqueries to find first session for each user, then count streaming sessions
CTE with Two-Pass Logic	O(n log n)	O(n)	Use CTEs to first identify viewer-first users, then count their streaming sessions

Single Window Function Query (Optimal) — Algorithm Steps

Use FIRST_VALUE() window function to get the session_type of the earliest session for each user
Filter results where the first session type was 'Viewer'
Count 'Streamer' sessions using conditional aggregation
Group by user_id to aggregate per user
Order results by streaming count and user_id in descending order

Visualization

Tap to expand

Step-by-Step Walkthrough

Window Function

FIRST_VALUE adds first session type to each row

Filter

Keep only rows where first session was 'Viewer'

Aggregate

Count streaming sessions per user

Code -

solution.c — C

// SQL Solution using single window function query (optimal)
/*
SELECT 
    user_id,
    COUNT(CASE WHEN session_type = 'Streamer' THEN 1 END) as streaming_sessions
FROM (
    SELECT user_id, session_type,
           FIRST_VALUE(session_type) OVER (
               PARTITION BY user_id 
               ORDER BY session_start
           ) as first_session_type
    FROM Sessions
) t
WHERE first_session_type = 'Viewer'
GROUP BY user_id
ORDER BY streaming_sessions DESC, user_id DESC;
*/

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass with sorting for window function - optimal for this problem type

⚡ Linearithmic

Space Complexity

O(n)

Space needed for window function computation and final result set

⚡ Linearithmic Space

43.6K Views

High Frequency

~25 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Single Window Function Query (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler