Max Concurrent Sessions — Solution & ExplanationProblems / Max Concurrent Sessions / EditorialApproach

Event decomposition. Each interval becomes two point events: (start, +1), (end, -1) — built with UNION ALL of two projections.

Sweep. A running SUM(delta) over the stream ordered by (event_time, delta) reconstructs the concurrency level at every boundary. The delta tiebreaker is doing real semantic work: -1 < +1, so ends process first at shared instants, encoding "touching intervals don't overlap".

Peak. MAX over the running column — window first (CTE), aggregate second.

Key InsightThe sweep line converts a question about intervals (which overlap whom?) into a question about deltas (when does the count change?), collapsing O(n²) pairwise comparison into sort + prefix sum. It's the same reduction operating theaters, meeting-room bookings, connection pools, and IP-range auditors use. And the subtlest line in the query is the humble tiebreaker: flip it to starts-first and every hand-off inflates the peak by one — a bug invisible on data without shared timestamps, which is why the dataset ships one.Hints

Comparing every session against every other is O(n^2) and painful to reason about. Reframe: concurrency only CHANGES at starts (+1) and ends (-1), so the whole timeline is just a stream of deltas.

UNION ALL two projections of the same table — starts as +1, ends as -1 — then a running SUM over the time-ordered stream is the live concurrency at every instant.

The tie-break carries the semantics: ORDER BY event_time, delta sorts -1 before +1 at the same timestamp, so back-to-back sessions (one ends at 10:00, another starts at 10:00) never double-count. The data plants exactly that hand-off.

The peak is MAX over the running column — a plain aggregate over the window result, which needs the window in a CTE or subquery first.

Solve Max Concurrent Sessions yourself →