Solution

To identify the number of streaming sessions for users whose first session was as a viewer, we analyze the Sessions table. We can efficiently accomplishes this by leveraging Common Table Expressions (CTEs), window functions, and conditional aggregations. Below is a comprehensive breakdown of the approach, step-by-step execution, and detailed explanations for each line of the SQL query.

Approach Overview

1. Determine the First Session of Each User:

   • Use a window function to rank sessions based on the session_start time for each user.
   • Identify users whose first session (rank = 1) was as a viewer.

2. Count Streaming Sessions for Qualified Users:

   • For users identified in the first step, count the number of sessions where session_type is Streamer.

3. Filter and Order the Results:

   • Ensure that only users who have both Viewer and Streamer session types are considered.
   • Order the final results by the count of streaming sessions in descending order and by user_id in descending order in case of ties.

SQL Query

WITH cte AS (
    SELECT 
        user_id,
        session_type,
        RANK() OVER (
            PARTITION BY user_id
            ORDER BY session_start
        ) AS rnk
    FROM Sessions
)
SELECT 
    user_id,
    SUM(CASE WHEN session_type = 'Streamer' THEN 1 ELSE 0 END) AS sessions_count
FROM Sessions
WHERE user_id IN (
    SELECT user_id
    FROM cte
    WHERE rnk = 1 AND session_type = 'Viewer'
)
GROUP BY user_id
HAVING COUNT(DISTINCT session_type) = 2
ORDER BY sessions_count DESC, user_id DESC;

Step-by-Step Approach

Step 1: Determine the First Session of Each User (cte)

Identify the first session (rnk = 1) of each user to determine if it was a viewer session.

SQL Snippet:

WITH cte AS (
    SELECT 
        user_id,
        session_type,
        RANK() OVER (
            PARTITION BY user_id
            ORDER BY session_start
        ) AS rnk
    FROM Sessions
)

Explanation:

1. WITH cte AS (

   • Initiates a Common Table Expression (CTE) named cte. CTEs are temporary result sets that can be referenced within the main query.

2. SELECT user_id, session_type,

   • Selects the user_id and session_type columns from the Sessions table.

3. RANK() OVER (

   • Applies the RANK() window function to assign a rank to each session based on the session_start time.

4. PARTITION BY user_id

   • Divides the data into partitions for each user_id. The ranking will reset for each user.

5. ORDER BY session_start

   • Orders the sessions within each partition by the session_start datetime in ascending order. The earliest session gets the highest priority.

Intermediate Output After Step 1 (cte):

+---------+--------------+-----+
| user_id | session_type | rnk |
+---------+--------------+-----+
| 101     | Viewer       | 1   |
| 101     | Streamer     | 2   |
| 101     | Streamer     | 3   |
| 101     | Streamer     | 4   |
| 102     | Lose         | 1   |
| 102     | Lose         | 2   |
| 103     | Streamer     | 1   |
| 104     | Viewer       | 1   |
+---------+--------------+-----+

Step 2: Identify Users Whose First Session Was as a Viewer

Select users whose first session (rnk = 1) was as a viewer.

SQL Snippet:

SELECT user_id
FROM cte
WHERE rnk = 1 AND session_type = 'Viewer'

Explanation:

1. SELECT user_id

   • Selects the user_id from the cte.

2. FROM cte

   • Specifies the CTE cte as the data source.

3. WHERE rnk = 1 AND session_type = 'Viewer'

   • Filters the records to include only those where the rank is 1 (first session) and the session type is 'Viewer'.

Intermediate Output After Step 2:

+---------+
| user_id |
+---------+
| 101     |
| 104     |
+---------+

Step 3: Count Streaming Sessions for Qualified Users

For the users identified in the previous step (whose first session was as a Viewer), count the number of sessions where session_type is Streamer.

SQL Snippet:

SELECT 
    user_id,
    SUM(CASE WHEN session_type = 'Streamer' THEN 1 ELSE 0 END) AS sessions_count
FROM Sessions
WHERE user_id IN (
    SELECT user_id
    FROM cte
    WHERE rnk = 1 AND session_type = 'Viewer'
)
GROUP BY user_id
HAVING COUNT(DISTINCT session_type) = 2
ORDER BY sessions_count DESC, user_id DESC;

Explanation:

1. SELECT user_id,

   • Selects the user_id from the Sessions table.

2. SUM(CASE WHEN session_type = 'Streamer' THEN 1 ELSE 0 END) AS sessions_count

   • Uses a CASE statement to assign 1 for Streamer sessions and 0 otherwise.
   • Sums these values to count the total number of Streamer sessions for each user.
   • Aliases the sum as sessions_count.

3. Subquery:

   SELECT user_id
   FROM cte
   WHERE rnk = 1 AND session_type = 'Viewer'

   • As explained in Step 2, selects users whose first session was as a Viewer.

4. GROUP BY user_id

   • Groups the data by user_id to perform the aggregation for each user.

5. HAVING COUNT(DISTINCT session_type) = 2

   • Ensures that only users who have both Viewer and Streamer sessions are included.
   • This condition filters out users who have only Viewer or only Streamer sessions.

6. ORDER BY sessions_count DESC, user_id DESC;

   • Orders the final results first by the count of streaming sessions in descending order.
   • In case of ties, orders by user_id in descending order.

Final Output:

+---------+----------------+
| user_id | sessions_count |
+---------+----------------+
| 101     | 2              |
+---------+----------------+