Peak Concurrent Game Sessions per Server Region
Capacity planning needs each server region's peak concurrency: the largest number of game sessions open at the same moment.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #194
Problem statement
Capacity planning needs each server region's peak concurrency: the largest number of game sessions open at the same moment. A session is open from started_at up to but not including ended_at — a session that ends at 21:00:00 does not overlap one that starts at 21:00:00. A NULL ended_at is a session still live, open from its start onwards.
For each server_region with at least one session, return server_region, peak_concurrent and peak_at — the earliest moment the peak was reached (it is always some session's started_at). Order by server_region.
Tables
Table: GameSession
| Column | Type |
|---|---|
| session_id | int |
| player_id | int |
| server_region | varchar |
| started_at | datetime |
| ended_at | datetime |
Primary key: session_id.
One row per connected session. ended_at is after started_at, or NULL for a session still in progress.
Examples
Example 1
GameSession
| session_id | player_id | server_region | started_at | ended_at |
|---|---|---|---|---|
| 1 | 41 | ap-south-1 | 2025-04-12 20:00:00 | 2025-04-12 21:00:00 |
| 2 | 42 | ap-south-1 | 2025-04-12 20:30:00 | 2025-04-12 22:00:00 |
| 3 | 43 | ap-south-1 | 2025-04-12 21:00:00 | 2025-04-12 21:45:00 |
| 4 | 44 | ap-south-1 | 2025-04-12 21:30:00 | NULL |
| 5 | 45 | eu-west-1 | 2025-04-12 18:00:00 | 2025-04-12 19:00:00 |
| 6 | 46 | eu-west-1 | 2025-04-12 19:00:00 | 2025-04-12 19:30:00 |
| 7 | 47 | us-east-1 | 2025-04-12 10:00:00 | 2025-04-12 12:00:00 |
| 8 | 48 | us-east-1 | 2025-04-12 10:15:00 | 2025-04-12 11:00:00 |
Output
| server_region | peak_concurrent | peak_at |
|---|---|---|
| ap-south-1 | 3 | 2025-04-12 21:30:00 |
| eu-west-1 | 1 | 2025-04-12 18:00:00 |
| us-east-1 | 2 | 2025-04-12 10:15:00 |
How to solve Peak Concurrent Game Sessions per Server Region
The sweep line: split every session into a +1 event at started_at and a −1 event at ended_at (a live session has no −1). A running SUM(delta) per region, in time order, is the number of open sessions right after each event, and its maximum is the peak.
Order matters at equal timestamps. Because a session is open up to but not including its end, an end at 21:00 must be processed before a start at 21:00; sorting by (ts, delta) puts −1 first. Adding session_id makes the order total, and with an explicit ROWS frame the running sum is deterministic. Several starts in the same second pass through intermediate counts, but those are below the final count at that second, so the maximum and its earliest time are unaffected. In ap-south-1 the peak is 3 at 21:30 — session 1 ended exactly when session 3 started, so 21:00 is still 2.
The alternative relies on concurrency only rising at a start: for each session, count the sessions of its region with started_at <= t and (ended_at > t or NULL) — a correlated count, quadratic but simple — and take the best per region with its earliest start. The sweep is one sort of 2n events.
Reference solution (MySQL)
WITH events AS (
SELECT server_region, session_id, started_at AS ts, 1 AS delta FROM GameSession
UNION ALL
SELECT server_region, session_id, ended_at AS ts, -1 AS delta FROM GameSession WHERE ended_at IS NOT NULL
), running AS (
SELECT server_region, ts,
SUM(delta) OVER (PARTITION BY server_region ORDER BY ts, delta, session_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS open_now
FROM events
), peaks AS (
SELECT server_region, MAX(open_now) AS peak_concurrent FROM running GROUP BY server_region
)
SELECT p.server_region, p.peak_concurrent, MIN(r.ts) AS peak_at
FROM peaks p
JOIN running r ON r.server_region = p.server_region AND r.open_now = p.peak_concurrent
GROUP BY p.server_region, p.peak_concurrent
ORDER BY p.server_regionAnother way
WITH at_start AS (
SELECT s.server_region, s.started_at AS ts,
(SELECT COUNT(*) FROM GameSession o
WHERE o.server_region = s.server_region AND o.started_at <= s.started_at
AND (o.ended_at IS NULL OR o.ended_at > s.started_at)) AS open_now
FROM GameSession s
)
SELECT a.server_region, a.open_now AS peak_concurrent, MIN(a.ts) AS peak_at
FROM at_start a
WHERE a.open_now = (SELECT MAX(b.open_now) FROM at_start b WHERE b.server_region = a.server_region)
GROUP BY a.server_region, a.open_now
ORDER BY a.server_region← Battle Pass Race to Tier 30, Season by Season · Median Completed Match Duration by Game Mode →