Create a Session Bar Chart - Solution & Explanation

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.