00:00:00

Description

Editorial

Viewer-to-Streamer Session Conversion

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Sessions =

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

Loading problem...

101

00:00:00

Description

Editorial

Viewer-to-Streamer Session Conversion

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Sessions =

Viewer-to-Streamer Session Conversion

Hints

Viewer-to-Streamer Session Conversion

Hints