Viewers Turned Streamers — LeetCode 2995 Python Solution

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).

+---------------+----------+
| 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.

import duckdb
import pandas as pd

def solution(sessions: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Sessions", sessions)
    return con.execute("""WITH
    T AS (
        SELECT
            user_id,
            session_type,
            RANK() OVER (
                PARTITION BY user_id
                ORDER BY session_start
            ) AS rk
        FROM Sessions
    )
SELECT user_id, COUNT(1) AS sessions_count
FROM
    T AS t
    JOIN Sessions AS s USING (user_id)
WHERE rk = 1 AND t.session_type = 'Viewer' AND s.session_type = 'Streamer'
GROUP BY 1
ORDER BY 2 DESC, 1 DESC;""").df()