User Activities within Time Bounds - Problem

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 identifier
  • session_start and session_end: Session timing
  • session_id: Unique session identifier
  • session_type: Either 'Viewer' or 'Streamer'

Return the result ordered by user_id in ascending order.

Table Schema

Sessions
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'
Primary Key: session_id
Note: Each session has a unique session_id and belongs to a user with a specific type

Input & Output

Example 1 — Multiple Users with Different Session Patterns
Input Table:
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
Output:
user_id
102
103
💡 Note:

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.
Example 2 — Edge Case with Exact 12-Hour Gap
Input Table:
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
Output:
user_id
201
💡 Note:

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_id is unique for each session
  • session_type is either 'Viewer' or 'Streamer'
  • session_start < session_end for all sessions
  • Time gap is measured between session start times

Visualization

Tap to expand
User Activities within Time Bounds: Problem OverviewSessions Inputuserstartidtype10213:003View10215:004View10320:0010View10309:0011ViewSELF JOINsame user+typeSession Pairsusergap_hours1022103131031FILTER≤ 12 hoursQualifying Usersuser_id102103Time Gap Calculation Logic1. Self-join Sessions table on same user_id and session_type2. Calculate time difference: |session_start_1 - session_start_2|3. Convert to hours and filter: time_diff ≤ 12 hours4. Select distinct users meeting the criteriaExample: User 102 sessions at 13:00 and 15:00 → 2 hour gap ✓
Understanding the Visualization
1
Input Sessions
Table with user sessions and timestamps
2
Compare Pairs
Self-join or window function to find session pairs
3
Time Filter
Keep users with ≤12 hour gaps
4
Output Users
Distinct qualifying user IDs
Key Takeaway
🎯 Key Insight: Use self-join or LAG window function to compare session pairs of the same user and type within time constraints

Related Problems

Asked in
Meta 28 Google 24 Amazon 19 Netflix 15
23.4K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen