Given a Sessions table with user session information, find all users who have had at least two sessions of the same type (either 'Viewer' or 'Streamer') with a maximum gap of 12 hours between sessions.
The table contains:
user_id: User identifiersession_startandsession_end: Session timingsession_id: Unique session identifiersession_type: Either 'Viewer' or 'Streamer'
Return the result ordered by user_id in ascending order.
Table Schema
| Column Name | Type | Description |
|---|---|---|
user_id
|
int | User identifier |
session_start
|
datetime | Session start timestamp |
session_end
|
datetime | Session end timestamp |
session_id
PK
|
int | Unique session identifier |
session_type
|
enum | Session type: 'Viewer' or 'Streamer' |
Input & Output
| user_id | session_start | session_end | session_id | session_type |
|---|---|---|---|---|
| 101 | 2023-11-01 08:00:00 | 2023-11-01 09:00:00 | 1 | Viewer |
| 101 | 2023-11-01 10:00:00 | 2023-11-01 11:00:00 | 2 | Streamer |
| 102 | 2023-11-01 13:00:00 | 2023-11-01 14:00:00 | 3 | Viewer |
| 102 | 2023-11-01 15:00:00 | 2023-11-01 16:00:00 | 4 | Viewer |
| 101 | 2023-11-02 09:00:00 | 2023-11-02 10:00:00 | 5 | Viewer |
| 102 | 2023-11-02 12:00:00 | 2023-11-02 13:00:00 | 6 | Streamer |
| 101 | 2023-11-02 13:00:00 | 2023-11-02 14:00:00 | 7 | Streamer |
| 102 | 2023-11-02 16:00:00 | 2023-11-02 17:00:00 | 8 | Viewer |
| 103 | 2023-11-01 08:00:00 | 2023-11-01 09:00:00 | 9 | Viewer |
| 103 | 2023-11-02 20:00:00 | 2023-11-02 23:00:00 | 10 | Viewer |
| 103 | 2023-11-03 09:00:00 | 2023-11-03 10:00:00 | 11 | Viewer |
| user_id |
|---|
| 102 |
User 101: Has sessions of different types (Viewer and Streamer) but no two sessions of the same type within 12 hours.
User 102: Has Viewer sessions (IDs 3 and 4) with only 2 hours gap, qualifying the user.
User 103: Has multiple Viewer sessions, including sessions 10 and 11 with 13 hours gap, but since they have qualifying pairs elsewhere, user 103 is included.
| user_id | session_start | session_end | session_id | session_type |
|---|---|---|---|---|
| 201 | 2023-11-01 08:00:00 | 2023-11-01 09:00:00 | 1 | Viewer |
| 201 | 2023-11-01 20:00:00 | 2023-11-01 21:00:00 | 2 | Viewer |
| 202 | 2023-11-01 10:00:00 | 2023-11-01 11:00:00 | 3 | Streamer |
| 202 | 2023-11-01 22:01:00 | 2023-11-01 23:00:00 | 4 | Streamer |
| user_id |
|---|
| 201 |
User 201: Has exactly 12 hours gap between Viewer sessions (8:00 to 20:00), which meets the ≤12 hours criteria.
User 202: Has 12 hours and 1 minute gap between Streamer sessions, which exceeds the 12-hour limit.
Constraints
-
1 ≤ user_id ≤ 10000 -
session_idis unique for each session -
session_typeis either'Viewer'or'Streamer' -
session_start < session_endfor all sessions - Time gap is measured between session start times