Skip to main content
Talentd

Create a Session Bar Chart - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min readAsked at: Twitch
Practice this problem

Problem Statement

Table: Sessions

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| session_id          | int     |
| duration            | int     |
+---------------------+---------+
session_id is the column of unique values for this table.
duration is the time in seconds that a user has visited the application.

 

You want to know how long a user visits your application. You decided to create bins of "[0-5>", "[5-10>", "[10-15>", and "15 minutes or more" and count the number of sessions on it.

Write a solution to report the (bin, total).

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Sessions table:
+-------------+---------------+
| session_id  | duration      |
+-------------+---------------+
| 1           | 30            |
| 2           | 199           |
| 3           | 299           |
| 4           | 580           |
| 5           | 1000          |
+-------------+---------------+
Output: 
+--------------+--------------+
| bin          | total        |
+--------------+--------------+
| [0-5>        | 3            |
| [5-10>       | 1            |
| [10-15>      | 0            |
| 15 or more   | 1            |
+--------------+--------------+
Explanation: 
For session_id 1, 2, and 3 have a duration greater or equal than 0 minutes and less than 5 minutes.
For session_id 4 has a duration greater or equal than 5 minutes and less than 10 minutes.
There is no session with a duration greater than or equal to 10 minutes and less than 15 minutes.
For session_id 5 has a duration greater than or equal to 15 minutes.

Approach Overview

Problem Overview: The task asks you to build a bar chart of session durations. Each session duration must be placed into one of four buckets: 0-5, 5-10, 10-15, and 15 or more. The query must return the count of sessions in each bucket, even if a bucket has zero sessions.

Approach 1: CASE + GROUP BY (O(n) time, O(1) space)

Scan the Sessions table and classify each row into a duration bucket using a CASE expression. The expression converts raw durations into labels such as '0-5' or '15 or more'. After assigning a label, use GROUP BY to aggregate and count sessions per bucket. The query performs a single table scan, so the time complexity is O(n) where n is the number of sessions, and it uses constant auxiliary space. This approach relies on standard SQL categorization and aggregation, which makes it easy to read and efficient for large datasets. See related concepts in SQL and database queries.

Approach 2: Conditional Aggregation with Predefined Buckets (O(n) time, O(1) space)

Another common pattern is conditional aggregation. Instead of grouping by a computed label, compute each bucket’s count directly using expressions like SUM(duration < 5) or SUM(duration >= 5 AND duration < 10). Each expression evaluates to 1 or 0 per row, and the sums produce counts for each category. This approach still scans the table once, giving O(n) time complexity and constant space usage. It avoids GROUP BY and gives you explicit control over each bar in the chart. Conditional aggregation is widely used when building dashboards or analytics queries that compute multiple metrics in a single pass. Related techniques appear frequently with GROUP BY and aggregation problems.

Approach 3: UNION ALL for Explicit Buckets (O(n) time, O(1) space)

You can also generate each bucket with its own query and combine them using UNION ALL. Each subquery filters the Sessions table using a duration condition and returns the corresponding label and count. This guarantees that every bucket appears in the result set even if the count is zero. Although the database optimizer may still scan efficiently, logically it performs multiple filtered aggregations. The complexity remains roughly O(n) for typical engines, but the query is longer and less flexible than conditional aggregation.

Recommended for interviews: The CASE + GROUP BY solution is the most natural and readable. It shows that you understand categorization with CASE and aggregation in SQL. Conditional aggregation is also strong because it demonstrates how to compute multiple metrics in a single pass. Interviewers typically expect one of these two patterns when solving database histogram or bucketing problems.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
CASE + GROUP BYO(n)O(1)Standard SQL bucketing problems where rows must be categorized then counted
Conditional AggregationO(n)O(1)When computing several metrics or histogram buckets in one pass
UNION ALL Bucket QueriesO(n)O(1)When explicit control over each output row is required, even if counts are zero

Video Solution

LeetCode 1435 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 6,443 views views

Frequently Asked Questions

Is Create a Session Bar Chart easy or hard?
Create a Session Bar Chart is classified as an Easy database problem with a high acceptance rate around 75%. The main requirement is understanding how to categorize values into ranges and count them using SQL aggregation.
Create a Session Bar Chart Python/Java solution
This problem is a SQL database query rather than a typical algorithm implemented in Python or Java. The solution is written in MySQL using CASE expressions, GROUP BY, or conditional aggregation to compute the counts for each duration range.
How to solve Create a Session Bar Chart in O(n)?
Perform a single pass over the Sessions table and classify durations into bins using CASE or conditional aggregation. Each row contributes to exactly one bucket, and SQL aggregation functions such as COUNT or SUM compute totals. Because the table is scanned once, the overall complexity remains O(n).
What is the best approach for Create a Session Bar Chart?
The most common solution uses a CASE expression to convert session durations into buckets and then aggregates them with GROUP BY. This performs a single table scan with O(n) time complexity and constant space. Conditional aggregation using SUM with boolean expressions is another equally efficient approach.
Is Create a Session Bar Chart asked at Google/Amazon/Meta?
Histogram-style SQL questions that group values into ranges appear frequently in interviews at large tech companies. Variations of this problem test your understanding of CASE expressions, aggregation, and analytical reporting queries used in real production databases.
What data structure is used in Create a Session Bar Chart?
The problem relies on SQL aggregation rather than traditional data structures. The database engine maintains counters for each duration bucket during the table scan, similar to how a histogram stores counts for predefined ranges.
What is the time complexity of Create a Session Bar Chart?
The query typically scans the Sessions table once to categorize durations and count rows. This results in O(n) time complexity where n is the number of sessions. Since aggregation uses only a few counters, the extra space complexity is O(1).

Ready to solve this problem?

Practice Create a Session Bar Chart with our built-in code editor and test cases.

Practice on FleetCode
Previous

Count Salary Categories