Create a Session Bar Chart

Create a Session Bar Chart

What is this problem about?

The Create a Session Bar Chart interview question is a SQL-based data reporting task. You are given a table of user sessions with their durations. You need to group these sessions into four specific duration buckets: [0-5), [5-10), [10-15), and 15 or more minutes. For each bucket, you must report the total number of sessions.

Why is this asked in interviews?

Twitch and other streaming platforms use this database interview pattern to test a candidate's ability to generate custom aggregates. It requires moving beyond simple GROUP BY operations on existing columns and instead creating "virtual" groups using conditional logic. It evaluates proficiency with CASE WHEN statements and the ability to ensure all categories appear in the final output, even if they have zero counts.

Algorithmic pattern used

This problem uses Conditional Aggregation and Static Row Generation.

1. Binning: Use a CASE WHEN statement to label each session duration into one of the four categories.
2. Union or Join: Since a standard GROUP BY would omit categories with zero sessions, you should either:
   • Create a CTE or temporary table with all four category names and LEFT JOIN it with the grouped session data.
   • Use a series of UNION statements to ensure every bucket is present.

Example explanation

Session Durations: 2, 4, 7, 20

1. 2 and 4 fall into [0-5). Count = 2.
2. 7 falls into [5-10). Count = 1.
3. No sessions in [10-15). Count = 0.
4. 20 falls into 15 or more. Count = 1. Final table shows all 4 categories with their counts 2, 1, 0, 1.

Common mistakes candidates make

• Missing Buckets: Using a simple GROUP BY which results in the [10-15) bucket being missing entirely from the result instead of showing 0.
• Overlapping Ranges: Incorrectly defining the boundaries (e.g., using BETWEEN 5 AND 10 which includes 10 in two buckets).
• Hard-coding: Failing to make the logic flexible for different numbers of sessions.

Interview preparation tip

Master the "Static Table + Left Join" trick in SQL. It's the standard way to ensure that your reports include rows for categories that have no data in the underlying tables.