User Activities within Time Bounds - Practice Coding Problems

User Activities within Time Bounds - Problem

Database Hard

User Activities within Time Bounds

You're analyzing user session data for a streaming platform to identify highly engaged users. The platform supports two types of sessions: Viewer (watching content) and Streamer (creating content).

Your task is to find users who demonstrate consistent engagement by having at least two sessions of the same type within a 12-hour window. This helps identify dedicated viewers or content creators who are likely to be loyal platform users.

Key Requirements:
• Sessions must be of the same type (both Viewer or both Streamer)
• Maximum gap between sessions: 12 hours
• Return user IDs sorted in ascending order

Table: Sessions
Contains user session data with start/end times and session types

Input & Output

example_1.sql — Basic Case

$ Input: Sessions: [(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')]

› Output: [102]

💡 Note: User 101 has different session types (Viewer vs Streamer), so doesn't qualify. User 102 has two Viewer sessions 2 hours apart, which is within the 12-hour window.

example_2.sql — Multiple Qualifying Users

$ Input: Sessions: [(102,'2023-11-02 12:00:00','2023-11-02 13:00:00',6,'Streamer'), (102,'2023-11-02 16:00:00','2023-11-02 17:00:00',8,'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: [102, 103]

💡 Note: User 102 qualifies from previous Viewer sessions. User 103 has two Viewer sessions 10 hours apart (20:00 to 09:00 next day = 13 hours, but 23:00 to 09:00 = 10 hours from session end to start).

example_3.sql — Edge Case: Exactly 12 Hours

$ Input: Sessions: [(104,'2023-11-01 08:00:00','2023-11-01 09:00:00',12,'Streamer'), (104,'2023-11-01 20:00:00','2023-11-01 21:00:00',13,'Streamer')]

› Output: [104]

💡 Note: User 104 has two Streamer sessions exactly 12 hours apart (08:00 to 20:00), which meets the ≤12 hour requirement.

Visualization

Tap to expand

Understanding the Visualization

Group by User & Type

Organize sessions by user and session type (Viewer/Streamer)

Apply Time Window

Check if any two sessions of same type are within 12 hours

Identify Qualifying Users

Users with at least one qualifying pair are included in results

Key Takeaway

🎯 Key Insight: Use database indexes on (user_id, session_type, session_start) for optimal performance when checking time-based proximity conditions.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

With proper indexes, join operation is O(n log n). Without indexes, could degrade to O(n²)

⚡ Linearithmic

Space Complexity

O(n)

Space for result set and join operation intermediate results

⚡ Linearithmic Space

Constraints

1 ≤ number of sessions ≤ 10⁴
1 ≤ user_id ≤ 10⁶
session_start < session_end for all sessions
session_type ∈ {'Viewer', 'Streamer'}
All session_id values are unique

Asked in

N Netflix 45 a Amazon 38 f Meta 32 G Google 28

The optimal solution uses a self-join with time range conditions to efficiently find users with same-type sessions within 12 hours. Key insight: leverage database indexes on timestamps for O(n log n) performance instead of O(n²) brute force comparison.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with LAG/LEAD	O(n log n)	O(n)	Use SQL window functions to compare adjacent sessions of the same type
Optimized Self-Join with Range Condition	O(n log n)	O(n)	Single query with efficient self-join using indexed time range conditions
Brute Force (Nested Loops)	O(U × S²)	O(U × S)	Compare every session pair for each user to find same-type sessions within 12 hours

Window Function with LAG/LEAD — Algorithm Steps

Partition sessions by user_id and session_type
Order sessions by session_start time within each partition
Use LAG window function to get previous session time
Calculate time difference and filter within 12 hours

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition Data

Group by user and session type

Apply LAG

Get previous session timestamp

Calculate Gap

Check if within 12-hour window

Code -

solution.c — C

// SQL Solution using Window Functions - Two Pass Approach

/*
WITH SessionsWithLag AS (
  SELECT 
    user_id,
    session_start,
    session_type,
    LAG(session_start) OVER (
      PARTITION BY user_id, session_type 
      ORDER BY session_start
    ) as prev_session_start
  FROM Sessions
),
QualifiedUsers AS (
  SELECT DISTINCT user_id
  FROM SessionsWithLag
  WHERE prev_session_start IS NOT NULL
    AND TIMESTAMPDIFF(HOUR, prev_session_start, session_start) <= 12
)
SELECT user_id
FROM QualifiedUsers
ORDER BY user_id;
*/

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Dominated by sorting sessions by user, type, and time

⚡ Linearithmic

Space Complexity

O(n)

Space for sorting and window function computation

⚡ Linearithmic Space

Constraints

1 ≤ number of sessions ≤ 10⁴
1 ≤ user_id ≤ 10⁶
session_start < session_end for all sessions
session_type ∈ {'Viewer', 'Streamer'}
All session_id values are unique

23.5K Views

Medium-High Frequency

~25 min Avg. Time

847 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Function with LAG/LEAD — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler