Session Duration Bucket Distribution

Easy

A product analytics team records every application session in a fact table. Table: sessions session_id: unique identifier of a session duration: session length in seconds The dashboard needs a fixed distribution across four duration buckets: [0,5) minutes: 0 <= duration < 300 [5,10) minutes: 300 <= duration < 600 [10,15) minutes: 600 <= duration < 900 15+ minutes: duration >= 900 Task: Return the number of sessions in each bucket. Critical output rules: Return all four buckets even if a bucket has no sessions. Return exactly two columns: session_bucket, total_sessions. Order rows by bucket sequence: [0,5), [5,10), [10,15), 15+ Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

sessions:
| session_id | duration |
|------------|----------|
| 1          | 30       |
| 2          | 199      |
| 3          | 299      |
| 4          | 580      |
| 5          | 1000     |

Output

[
  {"session_bucket":"[0,5)","total_sessions":3},
  {"session_bucket":"[5,10)","total_sessions":1},
  {"session_bucket":"[10,15)","total_sessions":0},
  {"session_bucket":"15+","total_sessions":1}
]

Explanation

The first three sessions are below 5 minutes, session 4 is in [5,10), no session is in [10,15), and session 5 is in 15+.

Example 2

Input

sessions:
| session_id | duration |
|------------|----------|
| 10         | 0        |
| 11         | 300      |
| 12         | 600      |
| 13         | 900      |

Output

[
  {"session_bucket":"[0,5)","total_sessions":1},
  {"session_bucket":"[5,10)","total_sessions":1},
  {"session_bucket":"[10,15)","total_sessions":1},
  {"session_bucket":"15+","total_sessions":1}
]

Explanation

Boundary values belong to the lower-inclusive bucket except the final open-ended bucket where 900 maps to 15+.

Example 3

Input

sessions:
| session_id | duration |
|------------|----------|
| 21         | 1200     |
| 22         | 2500     |
| 23         | 901      |

Output

[
  {"session_bucket":"[0,5)","total_sessions":0},
  {"session_bucket":"[5,10)","total_sessions":0},
  {"session_bucket":"[10,15)","total_sessions":0},
  {"session_bucket":"15+","total_sessions":3}
]

Explanation

All sessions are at least 15 minutes long, so only the final bucket is non-zero.

Constraints

0 <= number of rows in sessions <= 200000
1 <= session_id <= 2147483647
session_id is unique within a dataset
0 <= duration <= 2147483647
Return exactly two columns: session_bucket, total_sessions
Always return exactly four rows, one per required bucket
Bucket boundaries are lower-inclusive and upper-exclusive, except final open-ended bucket
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: session_bucket, total_sessions