Viewer-to-Streamer Session Conversion

Hard

A live-platform analytics team tracks user behavior in a session ledger and needs a conversion report from passive viewing to active streaming. You are given one table: Sessions user_id: integer identifier of a user session_start: session start timestamp session_end: session end timestamp session_id: unique session identifier session_type: either "Viewer" or "Streamer" Conversion definition: Compute each user's first session using this deterministic order: session_start ASC, then session_id ASC. Keep only users whose first session_type is "Viewer". For each kept user, count sessions where session_type = "Streamer". Output requirements: Return exactly two columns in this order: user_id, sessions_count Exclude users whose streamer-session count is 0 Sort by sessions_count DESC, then user_id DESC Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Sessions:
| user_id | session_start       | session_end         | session_id | session_type |
|---------|---------------------|---------------------|------------|--------------|
| 101     | 2023-11-06 13:53:42 | 2023-11-06 14:05:42 | 375        | Viewer       |
| 101     | 2023-11-22 16:45:21 | 2023-11-22 20:39:21 | 594        | Streamer     |
| 102     | 2023-11-16 13:23:09 | 2023-11-16 16:10:09 | 777        | Streamer     |
| 102     | 2023-11-17 13:23:09 | 2023-11-17 16:10:09 | 778        | Streamer     |
| 101     | 2023-11-20 07:16:06 | 2023-11-20 08:33:06 | 315        | Streamer     |
| 104     | 2023-11-27 03:10:49 | 2023-11-27 03:30:49 | 797        | Viewer       |
| 103     | 2023-11-27 03:10:49 | 2023-11-27 03:30:49 | 798        | Streamer     |

Output

[
  {"user_id":101,"sessions_count":2}
]

Explanation

User 101 starts as Viewer and has two Streamer sessions. User 102 starts as Streamer, so they are excluded. User 104 starts as Viewer but has no Streamer sessions.

Example 2

Input

Sessions:
| user_id | session_start       | session_end         | session_id | session_type |
|---------|---------------------|---------------------|------------|--------------|
| 300     | 2024-03-01 09:00:00 | 2024-03-01 09:15:00 | 10         | Viewer       |
| 300     | 2024-03-01 09:00:00 | 2024-03-01 09:20:00 | 11         | Streamer     |
| 300     | 2024-03-01 10:30:00 | 2024-03-01 11:00:00 | 12         | Streamer     |
| 400     | 2024-03-02 08:00:00 | 2024-03-02 08:30:00 | 20         | Streamer     |
| 400     | 2024-03-02 08:00:00 | 2024-03-02 08:45:00 | 21         | Viewer       |
| 400     | 2024-03-02 12:00:00 | 2024-03-02 13:00:00 | 22         | Streamer     |

Output

[
  {"user_id":300,"sessions_count":2}
]

Explanation

For user 300, the first session is chosen by timestamp then session_id, so session_id 10 (Viewer) wins. For user 400, session_id 20 (Streamer) wins the tie, so user 400 is excluded.

Example 3

Input

Sessions:
| user_id | session_start       | session_end         | session_id | session_type |
|---------|---------------------|---------------------|------------|--------------|
| 501     | 2024-05-01 09:00:00 | 2024-05-01 09:25:00 | 900        | Viewer       |
| 501     | 2024-05-02 10:00:00 | 2024-05-02 11:00:00 | 901        | Streamer     |
| 777     | 2024-05-01 08:00:00 | 2024-05-01 08:40:00 | 920        | Viewer       |
| 777     | 2024-05-03 08:00:00 | 2024-05-03 08:20:00 | 921        | Streamer     |
| 777     | 2024-05-04 08:00:00 | 2024-05-04 08:20:00 | 922        | Streamer     |
| 620     | 2024-05-01 07:30:00 | 2024-05-01 08:00:00 | 930        | Viewer       |
| 620     | 2024-05-02 07:30:00 | 2024-05-02 08:00:00 | 931        | Streamer     |
| 620     | 2024-05-03 07:30:00 | 2024-05-03 08:00:00 | 932        | Streamer     |

Output

[
  {"user_id":777,"sessions_count":2},
  {"user_id":620,"sessions_count":2},
  {"user_id":501,"sessions_count":1}
]

Explanation

Users 777 and 620 tie on sessions_count, so user_id DESC puts 777 before 620.

Constraints

1 <= number of rows in Sessions <= 100,000
1 <= user_id <= 1,000,000,000
session_id is unique in each input dataset
session_type is either 'Viewer' or 'Streamer'
session_start and session_end are valid timestamps
session_start <= session_end
A user's first session must be resolved by: session_start ASC, session_id ASC
Output must include only users with sessions_count > 0
Sort output by sessions_count DESC, user_id DESC
SQL submissions are evaluated on SQLite 3.27.2
Pandas submissions must return a DataFrame with columns [user_id, sessions_count]

Sessions: | user_id | session_start | session_end | session_id | session_type | |---------|---------------------|---------------------|------------|--------------| | 101 | 2023-11-06 13:53:42 | 2023-11-06 14:05:42 | 375 | Viewer | | 101 | 2023-11-22 16:45:21 | 2023-11-22 20:39:21 | 594 | Streamer | | 102 | 2023-11-16 13:23:09 | 2023-11-16 16:10:09 | 777 | Streamer | | 102 | 2023-11-17 13:23:09 | 2023-11-17 16:10:09 | 778 | Streamer | | 101 | 2023-11-20 07:16:06 | 2023-11-20 08:33:06 | 315 | Streamer | | 104 | 2023-11-27 03:10:49 | 2023-11-27 03:30:49 | 797 | Viewer | | 103 | 2023-11-27 03:10:49 | 2023-11-27 03:30:49 | 798 | Streamer |

Sessions: | user_id | session_start | session_end | session_id | session_type | |---------|---------------------|---------------------|------------|--------------| | 300 | 2024-03-01 09:00:00 | 2024-03-01 09:15:00 | 10 | Viewer | | 300 | 2024-03-01 09:00:00 | 2024-03-01 09:20:00 | 11 | Streamer | | 300 | 2024-03-01 10:30:00 | 2024-03-01 11:00:00 | 12 | Streamer | | 400 | 2024-03-02 08:00:00 | 2024-03-02 08:30:00 | 20 | Streamer | | 400 | 2024-03-02 08:00:00 | 2024-03-02 08:45:00 | 21 | Viewer | | 400 | 2024-03-02 12:00:00 | 2024-03-02 13:00:00 | 22 | Streamer |

Sessions: | user_id | session_start | session_end | session_id | session_type | |---------|---------------------|---------------------|------------|--------------| | 501 | 2024-05-01 09:00:00 | 2024-05-01 09:25:00 | 900 | Viewer | | 501 | 2024-05-02 10:00:00 | 2024-05-02 11:00:00 | 901 | Streamer | | 777 | 2024-05-01 08:00:00 | 2024-05-01 08:40:00 | 920 | Viewer | | 777 | 2024-05-03 08:00:00 | 2024-05-03 08:20:00 | 921 | Streamer | | 777 | 2024-05-04 08:00:00 | 2024-05-04 08:20:00 | 922 | Streamer | | 620 | 2024-05-01 07:30:00 | 2024-05-01 08:00:00 | 930 | Viewer | | 620 | 2024-05-02 07:30:00 | 2024-05-02 08:00:00 | 931 | Streamer | | 620 | 2024-05-03 07:30:00 | 2024-05-03 08:00:00 | 932 | Streamer |