LeetCode 2995 - Viewers Turned Streamers

We are given a database table called Sessions, where each row represents one user session on a platform. Every session belongs to a specific userid, has a start and end timestamp, a unique sessionid, and a sessiontype that can be either "Viewer" or "Streamer".

leetcodeharddatabase

LeetCode Problem 2995

Difficulty: 🔴 Hard
Topics: Database

Solution

Problem Understanding

We are given a database table called Sessions, where each row represents one user session on a platform. Every session belongs to a specific user_id, has a start and end timestamp, a unique session_id, and a session_type that can be either "Viewer" or "Streamer".

The problem asks us to identify users whose very first session was as a viewer, and then count how many streaming sessions they had overall. The final result should include only users who satisfy both conditions:

  1. Their earliest session chronologically was a "Viewer" session.
  2. They have at least one "Streamer" session.

The output should contain:

  • user_id
  • sessions_count, which is the number of "Streamer" sessions for that user

The results must be sorted by:

  1. sessions_count in descending order
  2. user_id in descending order

The key challenge here is determining a user's first session. Since users may have multiple sessions of different types, we must identify the earliest session using session_start. Only after identifying whether the first session is a viewer session can we count streamer sessions.

An important detail is that users whose first session is "Streamer" must be excluded entirely, even if they later become viewers. Similarly, users whose first session is "Viewer" but never stream should also be excluded because the result only includes users with streaming sessions.

Since session_id is guaranteed to be unique, the table itself does not contain duplicate sessions. The main difficulty comes from grouping by user and correctly identifying the earliest session.

Important Edge Cases

A naive implementation can fail on several important scenarios.

A user may have only one session. If that session is "Viewer" and there are no streamer sessions, they should not appear in the result.

A user may have multiple sessions, but their earliest session is "Streamer". Even if later sessions are viewers, they must be excluded.

A user may have many mixed session types. We must count only "Streamer" sessions after confirming that the earliest session was "Viewer".

If multiple users have the same streamer count, sorting by user_id descending becomes important.

Approaches

Brute Force Approach

A brute force solution would process each user independently.

For every user, we could scan the entire Sessions table to find:

  1. The earliest session based on session_start
  2. The total number of "Streamer" sessions

This approach is correct because it explicitly evaluates every session for every user. However, it is inefficient because for each unique user we repeatedly traverse the full dataset.

If there are n sessions and u users, this could take O(u × n) time. In the worst case where every session belongs to a unique user, this becomes O(n²).

This repeated scanning makes the approach impractical for larger datasets.

Optimal Approach

The key insight is that we can compute everything in a single grouped pass using SQL window functions or aggregation.

We need two pieces of information per user:

  1. Their first session type
  2. Their count of streamer sessions

To determine the first session, we can use the ROW_NUMBER() window function partitioned by user_id and ordered by session_start.

This assigns rank 1 to the earliest session of each user.

Once we know which users started as viewers, we can count only their "Streamer" sessions using conditional aggregation.

This reduces repeated work and ensures we process the dataset efficiently.

Approach Time Complexity Space Complexity Notes
Brute Force O(n²) O(u) Repeatedly scans all sessions for every user
Optimal O(n log n) O(u) Uses sorting for window function and grouped aggregation

The O(n log n) cost comes from sorting rows within window functions, which databases typically optimize internally.

Algorithm Walkthrough

Optimal SQL Algorithm

  1. First, partition sessions by user_id and sort each user's sessions by session_start.

We do this because the problem depends entirely on identifying the first chronological session for every user. 2. Assign a row number to each session using ROW_NUMBER().

The earliest session gets row_number = 1, allowing us to easily identify the first session type. 3. Extract users whose first session type is "Viewer".

These are the only users eligible for the final result. 4. For those eligible users, count all sessions where session_type = 'Streamer'.

We use conditional aggregation to count only streamer sessions. 5. Exclude users with zero streaming sessions.

The problem expects only users who actually became streamers. 6. Sort the results by:

  • sessions_count DESC
  • user_id DESC

Why it works

The correctness relies on one important invariant: ROW_NUMBER() ordered by session_start always identifies the user's earliest session uniquely.

Once the earliest session is identified, filtering to users whose first session is "Viewer" guarantees we only include valid users. Counting "Streamer" sessions afterward ensures the final count is accurate.

Python Solution

Since this is a Database problem, LeetCode expects an SQL query rather than a Python class implementation. Below is the correct SQL solution.

SELECT
    s.user_id,
    COUNT(*) AS sessions_count
FROM Sessions s
JOIN (
    SELECT user_id
    FROM (
        SELECT
            user_id,
            session_type,
            ROW_NUMBER() OVER (
                PARTITION BY user_id
                ORDER BY session_start
            ) AS rn
        FROM Sessions
    ) ranked
    WHERE rn = 1
      AND session_type = 'Viewer'
) first_viewers
ON s.user_id = first_viewers.user_id
WHERE s.session_type = 'Streamer'
GROUP BY s.user_id
ORDER BY sessions_count DESC, s.user_id DESC;

Implementation Explanation

The inner query uses ROW_NUMBER() to rank sessions for each user based on session_start. Since we partition by user_id, every user's session timeline is processed independently.

We then filter to rows where rn = 1, which represents the earliest session. Among those, we keep only users whose first session type is "Viewer".

Next, we join these valid users back to the original Sessions table. This allows us to count all sessions of type "Streamer" for eligible users.

The COUNT(*) aggregation computes the number of streaming sessions for each qualifying user.

Finally, the results are ordered according to the problem statement.

Go Solution

Like the Python section, this problem is a SQL database problem, so Go code is not applicable for LeetCode submission. The correct solution is the SQL query below.

SELECT
    s.user_id,
    COUNT(*) AS sessions_count
FROM Sessions s
JOIN (
    SELECT user_id
    FROM (
        SELECT
            user_id,
            session_type,
            ROW_NUMBER() OVER (
                PARTITION BY user_id
                ORDER BY session_start
            ) AS rn
        FROM Sessions
    ) ranked
    WHERE rn = 1
      AND session_type = 'Viewer'
) first_viewers
ON s.user_id = first_viewers.user_id
WHERE s.session_type = 'Streamer'
GROUP BY s.user_id
ORDER BY sessions_count DESC, s.user_id DESC;

Go-specific Notes

This LeetCode problem belongs to the Database category, so no Go function implementation is required. The submission format expects a valid SQL query.

Worked Examples

Example 1

Input:

user_id session_start session_type
101 2023-11-06 13:53:42 Viewer
101 2023-11-22 16:45:21 Streamer
102 2023-11-16 13:23:09 Streamer
102 2023-11-17 13:23:09 Streamer
101 2023-11-20 07:16:06 Streamer
104 2023-11-27 03:10:49 Viewer
103 2023-11-27 03:10:49 Streamer

Step 1: Rank Sessions

After applying ROW_NUMBER():

user_id session_type rn
101 Viewer 1
101 Streamer 2
101 Streamer 3
102 Streamer 1
102 Streamer 2
103 Streamer 1
104 Viewer 1

Step 2: Keep Users Whose First Session Was Viewer

Eligible users:

user_id
101
104

Step 3: Count Streamer Sessions

user_id streamer_count
101 2
104 0

User 104 is excluded because they have no streaming sessions.

Final result:

user_id sessions_count
101 2

Complexity Analysis

Measure Complexity Explanation
Time O(n log n) Window function ordering requires sorting
Space O(u) Stores grouped user information

The dominant cost comes from sorting sessions within each user partition when computing ROW_NUMBER(). Aggregation and filtering are linear relative to the number of rows.

Test Cases

# Example 1
# User 101 starts as viewer and streams twice
assert solution == [(101, 2)]

# User starts as streamer, excluded
assert solution == []

# User starts as viewer but never streams
assert solution == []

# Multiple valid users with sorting
# Expected order: sessions_count DESC, user_id DESC
assert solution == [
    (202, 3),
    (101, 2),
    (100, 2)
]

# User switches back and forth
# Only streamer sessions counted
assert solution == [
    (50, 2)
]

# Single viewer session only
assert solution == []

# Multiple users tied on streamer count
assert solution == [
    (300, 1),
    (200, 1),
    (100, 1)
]
Test Why
Example 1 Validates standard scenario
First session is streamer Ensures exclusion logic works
Viewer with no streaming sessions Ensures zero streamers are filtered
Multiple valid users Verifies sorting order
Mixed viewer/streamer timeline Confirms only streamer sessions counted
Single session viewer Tests minimal input
Equal streamer counts Validates secondary sorting by user_id

Edge Cases

User Starts as Viewer but Never Streams

A user may satisfy the first requirement but fail the second. For example, a user with only one viewer session should not appear in the result. A careless implementation might include them with count 0, but the WHERE s.session_type = 'Streamer' condition naturally excludes them.

User Starts as Streamer

Some users may later become viewers, but the requirement explicitly says the first session must be as a viewer. By using ROW_NUMBER() ordered by session_start, we ensure only the earliest session determines eligibility.

Multiple Users With Equal Streaming Counts

Sorting can be easy to overlook. If two users have the same number of streaming sessions, the result must be ordered by user_id descending. The final ORDER BY sessions_count DESC, user_id DESC guarantees the required ordering.