Create a Session Bar Chart - Practice Coding Problems

Create a Session Bar Chart - Problem

Database Easy

Create a Session Bar Chart

You're building an analytics dashboard for a mobile application and need to create a session duration bar chart to visualize user engagement patterns.

Given a Sessions table where each row represents a user session with its duration in seconds, you need to categorize these sessions into predefined time bins and count how many sessions fall into each category.

Table: Sessions

Column Name	Type
session_id	int
duration	int

The time bins are:
• [0-5) - Less than 5 minutes (0-299 seconds)
• [5-10) - 5 to 10 minutes (300-599 seconds)
• [10-15) - 10 to 15 minutes (600-899 seconds)
• 15 minutes or more - 15+ minutes (900+ seconds)

Goal: Return a result table with two columns: bin (the time range) and total (count of sessions in that range). All bins must be included even if they have 0 sessions.

Input & Output

example_1.sql — Basic Sessions

$ Input: Sessions table: | session_id | duration | |------------|----------| | 1 | 30 | | 2 | 199 | | 3 | 299 | | 4 | 580 | | 5 | 900 |

› Output: | bin | total | |-------------------|-------| | [0-5> | 3 | | [5-10> | 1 | | [10-15> | 0 | | 15 minutes or more| 1 |

💡 Note: Sessions 1,2,3 (30s, 199s, 299s) are all under 5 minutes. Session 4 (580s) is between 5-10 minutes. No sessions in 10-15 minutes. Session 5 (900s = 15 minutes exactly) goes into the 15+ category.

example_2.sql — Edge Cases

$ Input: Sessions table: | session_id | duration | |------------|----------| | 1 | 0 | | 2 | 300 | | 3 | 600 | | 4 | 899 | | 5 | 3600 |

› Output: | bin | total | |-------------------|-------| | [0-5> | 1 | | [5-10> | 1 | | [10-15> | 1 | | 15 minutes or more| 2 |

💡 Note: Testing boundary conditions: 0s goes to [0-5>, 300s (exactly 5 min) goes to [5-10>, 600s (exactly 10 min) goes to [10-15>, 899s (14:59) stays in [10-15>, and 3600s (1 hour) goes to 15+ minutes.

example_3.sql — Empty Bins

$ Input: Sessions table: | session_id | duration | |------------|----------| | 1 | 50 | | 2 | 100 | | 3 | 2000 |

› Output: | bin | total | |-------------------|-------| | [0-5> | 2 | | [5-10> | 0 | | [10-15> | 0 | | 15 minutes or more| 1 |

💡 Note: All sessions fall into either the shortest ([0-5>) or longest (15+) bins, leaving the middle bins empty. This shows that all bins must appear in results even with 0 count.

Visualization

Tap to expand

Understanding the Visualization

Scan Sessions

Read each session duration from the table

Apply Rules

Use CASE WHEN to determine which time bin each session belongs to

Count Categories

SUM() function counts how many sessions fall into each bin

Format Output

Present results as bin name and total count pairs

Key Takeaway

🎯 Key Insight: CASE WHEN with conditional aggregation allows us to categorize and count data in multiple bins with a single table scan, making it much more efficient than separate queries for each bin.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the Sessions table to categorize and count all sessions

✓ Linear Growth

Space Complexity

O(1)

Only stores the aggregated counts, no additional data structures needed

✓ Linear Space

Constraints

1 ≤ session_id ≤ 10⁵
0 ≤ duration ≤ 10⁶ (duration in seconds)
All bins must be present in the result, even if they have 0 sessions
Results should be ordered by bin sequence: [0-5>, [5-10>, [10-15>, 15 minutes or more

Asked in

f Meta 45 G Google 38 a Amazon 32 N Netflix 28

The optimal solution uses CASE WHEN conditional aggregation with SUM() to categorize and count sessions in all time bins with just one table scan. This approach is much more efficient than multiple separate queries and easily scales to additional bins.

Common Approaches

Approach	Time	Space	Notes
✓ CASE WHEN Aggregation (Optimal)	O(n)	O(1)	Use CASE WHEN with SUM to categorize and count all bins in a single table scan
Multiple Queries Approach	O(4n)	O(1)	Run separate COUNT queries for each time bin and combine results

CASE WHEN Aggregation (Optimal) — Algorithm Steps

Use CASE WHEN to create conditional expressions for each time bin
Wrap each CASE WHEN in SUM() to count matching sessions
Execute all categorizations simultaneously in one query
Use UNION ALL to create the final bin-total format
Order results by bin sequence for consistent output

Visualization

Tap to expand

Step-by-Step Walkthrough

Single Scan

Read each session duration once

Categorize

CASE WHEN determines which bin each session belongs to

Count

SUM aggregates count for each bin simultaneously

Format

UNION ALL creates the final result format

Code -

solution.c — C

// Optimal: Single pass with CASE WHEN aggregation
#include <stdio.h>
#include <string.h>

char* createSessionBarChartOptimal() {
    static char query[] = 
        "SELECT bin, total FROM ( "
        "    SELECT '[0-5>' as bin, "
        "           SUM(CASE WHEN duration >= 0 AND duration < 300 THEN 1 ELSE 0 END) as total, "
        "           1 as order_num "
        "    FROM Sessions "
        
        "    UNION ALL "
        
        "    SELECT '[5-10>' as bin, "
        "           SUM(CASE WHEN duration >= 300 AND duration < 600 THEN 1 ELSE 0 END) as total, "
        "           2 as order_num "
        "    FROM Sessions "
        
        "    UNION ALL "
        
        "    SELECT '[10-15>' as bin, "
        "           SUM(CASE WHEN duration >= 600 AND duration < 900 THEN 1 ELSE 0 END) as total, "
        "           3 as order_num "
        "    FROM Sessions "
        
        "    UNION ALL "
        
        "    SELECT '15 minutes or more' as bin, "
        "           SUM(CASE WHEN duration >= 900 THEN 1 ELSE 0 END) as total, "
        "           4 as order_num "
        "    FROM Sessions "
        ") t "
        "ORDER BY order_num";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the Sessions table to categorize and count all sessions

✓ Linear Growth

Space Complexity

O(1)

Only stores the aggregated counts, no additional data structures needed

✓ Linear Space

Constraints

1 ≤ session_id ≤ 10⁵
0 ≤ duration ≤ 10⁶ (duration in seconds)
All bins must be present in the result, even if they have 0 sessions
Results should be ordered by bin sequence: [0-5>, [5-10>, [10-15>, 15 minutes or more

52.4K Views

High Frequency

~15 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

CASE WHEN Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler