Create a Session Bar Chart solution and explanation
The problem
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
SELECT '[0-5>' AS bin, COUNT(1) AS total FROM Sessions WHERE duration < 300
UNION
SELECT '[5-10>' AS bin, COUNT(1) AS total FROM Sessions WHERE 300 <= duration AND duration < 600
UNION
SELECT '[10-15>' AS bin, COUNT(1) AS total FROM Sessions WHERE 600 <= duration AND duration < 900
UNION
SELECT '15 or more' AS bin, COUNT(1) AS total FROM Sessions WHERE 900 <= duration;Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| CASE + GROUP BY | O(n) | O(1) | Standard SQL bucketing problems where rows must be categorized then counted |
| Conditional Aggregation | O(n) | O(1) | When computing several metrics or histogram buckets in one pass |
| UNION ALL Bucket Queries | O(n) | O(1) | When explicit control over each output row is required, even if counts are zero |
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).