LeetCode 1435 - Create a Session Bar Chart

The problem provides a database table named Sessions with two columns: | Column | Meaning | | --- | --- | | sessionid |

leetcodeeasydatabase

LeetCode Problem 1435

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

The problem provides a database table named Sessions with two columns:

Column Meaning
session_id Unique identifier for a session
duration Length of the session in seconds

We need to group sessions into predefined duration ranges, called bins, and count how many sessions fall into each bin.

The required bins are:

Bin Label Duration Range
[0-5> 0 minutes inclusive to less than 5 minutes
[5-10> 5 minutes inclusive to less than 10 minutes
[10-15> 10 minutes inclusive to less than 15 minutes
15 or more 15 minutes or greater

Since durations are stored in seconds, we must convert the minute boundaries into seconds:

Bin Label Seconds Range
[0-5> 0 <= duration < 300
[5-10> 300 <= duration < 600
[10-15> 600 <= duration < 900
15 or more duration >= 900

The output should contain every bin, even if the count is zero.

The problem is fundamentally a categorization and aggregation task. For each session, we determine which bucket it belongs to, then increment that bucket's count.

An important detail is that the result must include bins with zero sessions. A naive grouping query that only counts existing matches could accidentally omit empty bins.

The input size is small enough that performance is not a concern, but writing the SQL carefully matters because we must guarantee that all four categories appear in the final result.

Some important edge cases include:

  • Sessions exactly on boundaries such as 300, 600, or 900 seconds
  • Empty bins that still must appear in the result
  • Multiple sessions falling into the same bin
  • Very large durations that all belong in the "15 or more" category

Correct handling of inclusive and exclusive boundaries is the most common source of mistakes in this problem.

Approaches

Brute Force Approach

The brute-force idea is to scan the Sessions table separately for each bin.

For example:

  • One query counts durations between 0 and 299
  • Another counts durations between 300 and 599
  • Another counts durations between 600 and 899
  • Another counts durations greater than or equal to 900

Then we combine the four counts using UNION.

This approach is correct because every session belongs to exactly one bin, and every bin is counted independently.

However, it repeatedly scans the table multiple times. Even though the dataset is small in this problem, repeatedly traversing the same table is inefficient and unnecessary.

Optimal Approach

The better approach is to scan the table once and classify each row using conditional aggregation.

The key insight is that SQL aggregate functions can count rows conditionally using expressions like:

SUM(condition)

or

COUNT(CASE WHEN ... THEN 1 END)

This allows us to process all sessions in one pass while maintaining counts for every category simultaneously.

Since the problem also requires bins with zero counts, we explicitly construct the output rows and attach the aggregated counts.

Approach Comparison

Approach Time Complexity Space Complexity Notes
Brute Force O(4N) O(1) Scans the table once per bin
Optimal O(N) O(1) Single pass with conditional aggregation

Algorithm Walkthrough

Optimal SQL Algorithm

  1. Traverse the Sessions table once.

Each row contains a session duration in seconds. We determine which predefined range the duration belongs to. 2. Maintain four counters internally.

We need one counter for each bin:

  • [0-5>
  • [5-10>
  • [10-15>
  • 15 or more
  1. For every session, evaluate the duration ranges.

We check:

  • Is the duration less than 300?
  • Is it between 300 and 599?
  • Is it between 600 and 899?
  • Is it at least 900?
  1. Increment the matching counter.

Since the ranges are mutually exclusive, every session contributes to exactly one bin. 5. Return all four bins explicitly.

Even if a count is zero, the row must still appear in the result.

Why it works

The algorithm works because the four ranges partition all possible durations into non-overlapping categories.

Every duration satisfies exactly one condition:

  • Less than 300
  • Between 300 and 599
  • Between 600 and 899
  • At least 900

Since each session contributes to exactly one bucket, the final counts are correct.

Python Solution

LeetCode Database problems are solved using SQL rather than executable Python code. The following is the correct MySQL solution.

# This problem is solved using SQL, not executable Python code.

"""
SELECT '[0-5>' AS bin,
       COUNT(CASE WHEN duration < 300 THEN 1 END) AS total
FROM Sessions

UNION ALL

SELECT '[5-10>' AS bin,
       COUNT(CASE WHEN duration >= 300 AND duration < 600 THEN 1 END) AS total
FROM Sessions

UNION ALL

SELECT '[10-15>' AS bin,
       COUNT(CASE WHEN duration >= 600 AND duration < 900 THEN 1 END) AS total
FROM Sessions

UNION ALL

SELECT '15 or more' AS bin,
       COUNT(CASE WHEN duration >= 900 THEN 1 END) AS total
FROM Sessions;
"""

The solution performs four aggregations, one for each bin. Each query counts only the rows satisfying the corresponding duration range.

COUNT(CASE WHEN ... THEN 1 END) works because COUNT ignores NULL values. Rows matching the condition contribute 1, while non-matching rows contribute NULL.

UNION ALL is used instead of UNION because we do not need duplicate elimination. Using UNION ALL is slightly more efficient.

Each SELECT statement explicitly defines the bin label, ensuring that all bins appear in the output even if their counts are zero.

Go Solution

LeetCode Database problems do not require Go implementations because the submission language is SQL.

For completeness, here is the same logic expressed as a SQL string in Go.

package main

func query() string {
	return `
SELECT '[0-5>' AS bin,
       COUNT(CASE WHEN duration < 300 THEN 1 END) AS total
FROM Sessions

UNION ALL

SELECT '[5-10>' AS bin,
       COUNT(CASE WHEN duration >= 300 AND duration < 600 THEN 1 END) AS total
FROM Sessions

UNION ALL

SELECT '[10-15>' AS bin,
       COUNT(CASE WHEN duration >= 600 AND duration < 900 THEN 1 END) AS total
FROM Sessions

UNION ALL

SELECT '15 or more' AS bin,
       COUNT(CASE WHEN duration >= 900 THEN 1 END) AS total
FROM Sessions;
`
}

The Go version simply returns the SQL query as a raw string literal. No additional handling is necessary because the actual execution environment for this problem is SQL-based.

Worked Examples

Example 1

Input table:

session_id duration
1 30
2 199
3 299
4 580
5 1000

We process each row one by one.

session_id duration Matching Bin Counts After Processing
1 30 [0-5> [0-5> = 1
2 199 [0-5> [0-5> = 2
3 299 [0-5> [0-5> = 3
4 580 [5-10> [5-10> = 1
5 1000 15 or more 15 or more = 1

Final counts:

Bin Total
[0-5> 3
[5-10> 1
[10-15> 0
15 or more 1

Notice that [10-15> still appears even though no sessions belong to it.

Complexity Analysis

Measure Complexity Explanation
Time O(N) The table is scanned once
Space O(1) Only fixed-size counters are maintained

The query processes each session exactly once while evaluating a constant number of conditions. No additional data structures proportional to input size are required.

Test Cases

# Example case from the problem statement
sessions = [
    (1, 30),
    (2, 199),
    (3, 299),
    (4, 580),
    (5, 1000),
]
# Expected:
# [0-5> -> 3
# [5-10> -> 1
# [10-15> -> 0
# 15 or more -> 1

# Boundary value: exactly 300 seconds
sessions = [
    (1, 300),
]
# Expected:
# [5-10> -> 1

# Boundary value: exactly 600 seconds
sessions = [
    (1, 600),
]
# Expected:
# [10-15> -> 1

# Boundary value: exactly 900 seconds
sessions = [
    (1, 900),
]
# Expected:
# 15 or more -> 1

# All sessions in first bin
sessions = [
    (1, 10),
    (2, 20),
    (3, 299),
]
# Expected:
# [0-5> -> 3

# Empty table
sessions = []
# Expected:
# all bins appear with 0

# Large durations
sessions = [
    (1, 5000),
    (2, 10000),
]
# Expected:
# 15 or more -> 2

# Mixed categories
sessions = [
    (1, 50),
    (2, 350),
    (3, 700),
    (4, 1200),
]
# Expected:
# each bin has 1

Test Summary

Test Why
Problem example Verifies standard behavior
Duration = 300 Tests lower boundary of second bin
Duration = 600 Tests lower boundary of third bin
Duration = 900 Tests lower boundary of final bin
All in first bin Ensures repeated counting works
Empty table Verifies zero-count bins appear
Very large durations Ensures large values map correctly
One session per category Verifies all bins simultaneously

Edge Cases

Boundary Durations

Values exactly equal to 300, 600, and 900 seconds are easy places to introduce off-by-one errors.

For example:

  • 300 seconds must belong to [5-10>
  • 600 seconds must belong to [10-15>
  • 900 seconds must belong to 15 or more

The implementation handles this correctly by carefully using inclusive lower bounds and exclusive upper bounds.

Empty Bins

A common mistake is returning only bins that contain rows. However, the problem explicitly requires all four bins to appear, even if the count is zero.

The implementation avoids this issue by explicitly constructing four SELECT statements and combining them with UNION ALL.

Empty Table

If the Sessions table contains no rows, the query must still return:

Bin Total
[0-5> 0
[5-10> 0
[10-15> 0
15 or more 0

Since aggregate functions like COUNT return zero on empty input, the query naturally handles this case correctly.