00:00:00

Description

Editorial

Users with Repeat Session Type Within 12 Hours

HARD30 pts

A streaming platform analytics team wants to detect users who repeatedly return to the same kind of session (viewer or streamer) within a short time window.

You are given a session ledger:

Sessions

user_id: user identifier
session_start: session start timestamp
session_end: session end timestamp
session_id: unique session identifier
session_type: either "Viewer" or "Streamer"

Task: Find all users who satisfy the following condition.

For at least one of the two session types (Viewer or Streamer):

Consider only that user's sessions of that type.
Sort by session_start ascending, then session_id ascending.
If any adjacent pair has: current.session_start - previous.session_end <= 12 hours, then that user qualifies.

Output requirements:

Return exactly one column: user_id
Return unique user IDs only
Sort by user_id in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Sessions:
| user_id | session_start       | session_end         | session_id | session_type |
|---------|---------------------|---------------------|------------|--------------|
| 101     | 2024-01-01 08:00:00 | 2024-01-01 09:00:00 | 1          | Viewer       |
| 101     | 2024-01-01 11:00:00 | 2024-01-01 12:00:00 | 2          | Streamer     |
| 102     | 2024-01-02 07:00:00 | 2024-01-02 08:00:00 | 3          | Viewer       |
| 102     | 2024-01-02 10:00:00 | 2024-01-02 11:00:00 | 4          | Viewer       |
| 103     | 2024-01-03 09:00:00 | 2024-01-03 10:00:00 | 5          | Streamer     |
| 103     | 2024-01-04 23:30:00 | 2024-01-05 00:30:00 | 6          | Streamer     |

Output

[
  {"user_id": 102}
]

Explanation

User 102 has two Viewer sessions with a 2-hour gap (<= 12 hours). User 101 has no same-type qualifying pair. User 103 has a gap larger than 12 hours.

Example

Input

Sessions:
| user_id | session_start       | session_end         | session_id | session_type |
|---------|---------------------|---------------------|------------|--------------|
| 201     | 2024-02-01 07:00:00 | 2024-02-01 08:00:00 | 20         | Viewer       |
| 201     | 2024-02-01 20:00:00 | 2024-02-01 21:00:00 | 21         | Viewer       |
| 202     | 2024-02-02 07:00:00 | 2024-02-02 08:00:00 | 22         | Streamer     |
| 202     | 2024-02-02 20:00:01 | 2024-02-02 21:00:01 | 23         | Streamer     |

Output

[
  {"user_id": 201}
]

Explanation

Exactly 12 hours qualifies for user 201. A gap of 12 hours plus 1 second does not qualify for user 202.

Example

Input

Sessions:
| user_id | session_start       | session_end         | session_id | session_type |
|---------|---------------------|---------------------|------------|--------------|
| 301     | 2024-03-01 00:00:00 | 2024-03-01 00:10:00 | 100        | Viewer       |
| 301     | 2024-03-01 00:00:00 | 2024-03-01 23:30:00 | 101        | Viewer       |
| 301     | 2024-03-02 11:20:00 | 2024-03-02 12:00:00 | 102        | Viewer       |
| 302     | 2024-03-01 05:00:00 | 2024-03-01 05:20:00 | 110        | Streamer     |
| 302     | 2024-03-02 18:00:00 | 2024-03-02 18:30:00 | 111        | Streamer     |

Output

[
  {"user_id": 301}
]

Explanation

For user 301, deterministic ordering by session_start then session_id is important. User 302 has a same-type gap above 12 hours.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Sessions <= 250000
1 <= user_id <= 1000000000
session_id is unique within each dataset
session_type is either 'Viewer' or 'Streamer'
session_start and session_end use valid datetime format 'YYYY-MM-DD HH:MM:SS'
session_start <= session_end
Sort key inside each (user_id, session_type) partition is: session_start ASC, session_id ASC
A user qualifies if any adjacent same-type pair has gap <= 12 hours
Output must contain distinct user_id values sorted ascending
SQL submissions run on SQLite 3.27.2
Pandas submissions must return a DataFrame with column [user_id]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Sessions =

[{"user_id":101,"session_start":"2024-01-01 08:00:00","session_end":"2024-01-01 09:00:00","session_id":1,"session_type":"Viewer"},{"user_id":101,"session_start":"2024-01-01 11:00:00","session_end":"2024-01-01 12:00:00","session_id":2,"session_type":"Streamer"},{"user_id":102,"session_start":"2024-01-02 07:00:00","session_end":"2024-01-02 08:00:00","session_id":3,"session_type":"Viewer"},{"user_id":102,"session_start":"2024-01-02 10:00:00","session_end":"2024-01-02 11:00:00","session_id":4,"session_type":"Viewer"},{"user_id":103,"session_start":"2024-01-03 09:00:00","session_end":"2024-01-03 10:00:00","session_id":5,"session_type":"Streamer"},{"user_id":103,"session_start":"2024-01-04 23:30:00","session_end":"2024-01-05 00:30:00","session_id":6,"session_type":"Streamer"}]

Loading problem...

101

00:00:00

Description

Editorial

Users with Repeat Session Type Within 12 Hours

HARD30 pts

A streaming platform analytics team wants to detect users who repeatedly return to the same kind of session (viewer or streamer) within a short time window.

You are given a session ledger:

Sessions

user_id: user identifier
session_start: session start timestamp
session_end: session end timestamp
session_id: unique session identifier
session_type: either "Viewer" or "Streamer"

Task: Find all users who satisfy the following condition.

For at least one of the two session types (Viewer or Streamer):

Consider only that user's sessions of that type.
Sort by session_start ascending, then session_id ascending.
If any adjacent pair has: current.session_start - previous.session_end <= 12 hours, then that user qualifies.

Output requirements:

Return exactly one column: user_id
Return unique user IDs only
Sort by user_id in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Sessions:
| user_id | session_start       | session_end         | session_id | session_type |
|---------|---------------------|---------------------|------------|--------------|
| 101     | 2024-01-01 08:00:00 | 2024-01-01 09:00:00 | 1          | Viewer       |
| 101     | 2024-01-01 11:00:00 | 2024-01-01 12:00:00 | 2          | Streamer     |
| 102     | 2024-01-02 07:00:00 | 2024-01-02 08:00:00 | 3          | Viewer       |
| 102     | 2024-01-02 10:00:00 | 2024-01-02 11:00:00 | 4          | Viewer       |
| 103     | 2024-01-03 09:00:00 | 2024-01-03 10:00:00 | 5          | Streamer     |
| 103     | 2024-01-04 23:30:00 | 2024-01-05 00:30:00 | 6          | Streamer     |

Output

[
  {"user_id": 102}
]

Explanation

User 102 has two Viewer sessions with a 2-hour gap (<= 12 hours). User 101 has no same-type qualifying pair. User 103 has a gap larger than 12 hours.

Example

Input

Sessions:
| user_id | session_start       | session_end         | session_id | session_type |
|---------|---------------------|---------------------|------------|--------------|
| 201     | 2024-02-01 07:00:00 | 2024-02-01 08:00:00 | 20         | Viewer       |
| 201     | 2024-02-01 20:00:00 | 2024-02-01 21:00:00 | 21         | Viewer       |
| 202     | 2024-02-02 07:00:00 | 2024-02-02 08:00:00 | 22         | Streamer     |
| 202     | 2024-02-02 20:00:01 | 2024-02-02 21:00:01 | 23         | Streamer     |

Output

[
  {"user_id": 201}
]

Explanation

Exactly 12 hours qualifies for user 201. A gap of 12 hours plus 1 second does not qualify for user 202.

Example

Input

Sessions:
| user_id | session_start       | session_end         | session_id | session_type |
|---------|---------------------|---------------------|------------|--------------|
| 301     | 2024-03-01 00:00:00 | 2024-03-01 00:10:00 | 100        | Viewer       |
| 301     | 2024-03-01 00:00:00 | 2024-03-01 23:30:00 | 101        | Viewer       |
| 301     | 2024-03-02 11:20:00 | 2024-03-02 12:00:00 | 102        | Viewer       |
| 302     | 2024-03-01 05:00:00 | 2024-03-01 05:20:00 | 110        | Streamer     |
| 302     | 2024-03-02 18:00:00 | 2024-03-02 18:30:00 | 111        | Streamer     |

Output

[
  {"user_id": 301}
]

Explanation

For user 301, deterministic ordering by session_start then session_id is important. User 302 has a same-type gap above 12 hours.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Sessions <= 250000
1 <= user_id <= 1000000000
session_id is unique within each dataset
session_type is either 'Viewer' or 'Streamer'
session_start and session_end use valid datetime format 'YYYY-MM-DD HH:MM:SS'
session_start <= session_end
Sort key inside each (user_id, session_type) partition is: session_start ASC, session_id ASC
A user qualifies if any adjacent same-type pair has gap <= 12 hours
Output must contain distinct user_id values sorted ascending
SQL submissions run on SQLite 3.27.2
Pandas submissions must return a DataFrame with column [user_id]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Sessions =

Users with Repeat Session Type Within 12 Hours (Hard) — Practice with Code Visualizer | OneNoughtOne

Users with Repeat Session Type Within 12 Hours

Hints

Users with Repeat Session Type Within 12 Hours

Hints