Domain 1EasyCORE

Create a Session Bar Chart

The problem requires creating a session bar chart using SQL. Given a table that logs user sessions with timestamps for when they start and end, the task is to generate a bar chart that shows the number of active users at any given minute over a specified time range.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 0

1435. Create a Session Bar Chart

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 77.8%


Problem Breakdown

The problem requires creating a session bar chart using SQL. Given a table that logs user sessions with timestamps for when they start and end, the task is to generate a bar chart that shows the number of active users at any given minute over a specified time range.

Inputs:

  • A table Sessions with columns:
    • user_id: The ID of the user.
    • start_time: The timestamp when the session starts.
    • end_time: The timestamp when the session ends.

Outputs:

  • A bar chart represented as a list of tuples, where each tuple contains:
    • A minute within the specified time range.
    • The number of active users at that minute.

Implicit Constraints or Tricky Edge Cases:

  • Sessions can start and end at any minute within the given time range.
  • There might be overlapping sessions.
  • The table could be empty, resulting in an empty bar chart.
  • Sessions might span across multiple days, but we are only interested in a specific day’s data.

Intuition

The brute-force approach would involve iterating over each minute of the specified time range and counting the number of active users for that minute. This would require checking every session to see if it overlaps with the current minute, leading to a high time complexity of O(n * m), where n is the number of sessions and m is the number of minutes in the range.

A better approach involves transforming the problem into a series of events (session starts and ends) and then processing these events in chronological order. This reduces the complexity by focusing on significant changes (start/end of sessions) rather than every minute.

Approach

  1. Transform Sessions to Events:

    • For each session, create two events: one for the start time with a type “start” and another for the end time with a type “end”.
  2. Sort Events Chronologically:

    • Sort all these events by their timestamp.
  3. Process Events to Track Active Users:

    • Initialize a counter for active users.
    • Iterate through the sorted events:
      • If the event is of type “start”, increment the active users counter.
      • If the event is of type “end”, decrement the active users counter.
      • Record the number of active users at each minute.
  4. Generate Bar Chart:

    • For each unique minute in the range, record the number of active users.

Complexity Analysis

  • Time Complexity: O(n log n + m), where n is the number of sessions and m is the number of minutes in the specified time range. Sorting the events takes O(n log n), and processing them takes O(m).
  • Space Complexity: O(n + m) for storing the events and the result.

Solution

import pandas as pd

def create_session_bar_chart(sessions: pd.DataFrame, start_time: str, end_time: str) -> list:
    # Convert start_time and end_time to datetime objects
    start = pd.to_datetime(start_time)
    end = pd.to_datetime(end_time)
    
    # Create events for session starts and ends
    events = []
    for index, row in sessions.iterrows():
        events.append((row['start_time'], 'start'))
        events.append((row['end_time'], 'end'))
    
    # Sort events by timestamp
    events.sort()
    
    # Initialize variables to track active users and the bar chart
    active_users = 0
    bar_chart = {}
    
    # Process each event
    for time, event_type in events:
        if start <= time <= end:
            if event_type == 'start':
                active_users += 1
            elif event_type == 'end':
                active_users -= 1
            # Record the number of active users at this minute
            bar_chart[time] = active_users
    
    # Generate the result list from the bar chart dictionary
    result = [(minute, count) for minute, count in sorted(bar_chart.items())]
    
    return result

Edge Cases Handled

  • Empty Input: If the sessions table is empty, the function returns an empty list.
  • Overlapping Sessions: The algorithm correctly handles overlapping sessions by maintaining a running count of active users.
  • Sessions Spanning Multiple Days: The function only considers sessions within the specified time range (start_time to end_time).
  • No Active Users at Any Minute: If no users are active at any minute, the result will be an empty list or a list with zeros for all minutes.