Users with Repeat Session Type Within 12 Hours

Hard

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 1

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 2

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 3

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.

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]

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 |

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 |

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 |