Peak Concurrent Game Sessions per Server Region

Hard
194.

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. 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

ColumnType
session_idint
player_idint
server_regionvarchar
started_atdatetime
ended_atdatetime

Primary key: session_id.

One row per connected session. ended_at is after started_at, or NULL for a session still in progress.

Example

The rows must come back in the order shown.

GameSession

session_idplayer_idserver_regionstarted_atended_at
141ap-south-12025-04-12 20:00:002025-04-12 21:00:00
242ap-south-12025-04-12 20:30:002025-04-12 22:00:00
343ap-south-12025-04-12 21:00:002025-04-12 21:45:00
444ap-south-12025-04-12 21:30:00NULL
545eu-west-12025-04-12 18:00:002025-04-12 19:00:00
646eu-west-12025-04-12 19:00:002025-04-12 19:30:00
747us-east-12025-04-12 10:00:002025-04-12 12:00:00
848us-east-12025-04-12 10:15:002025-04-12 11:00:00

Output

server_regionpeak_concurrentpeak_at
ap-south-132025-04-12 21:30:00
eu-west-112025-04-12 18:00:00
us-east-122025-04-12 10:15:00
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
session_idint
player_idint
server_regionvarchar
started_atdatetime
ended_atdatetime

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_idplayer_idserver_regionstarted_atended_at
141ap-south-12025-04-12 20:00:002025-04-12 21:00:00
242ap-south-12025-04-12 20:30:002025-04-12 22:00:00
343ap-south-12025-04-12 21:00:002025-04-12 21:45:00
444ap-south-12025-04-12 21:30:00NULL
545eu-west-12025-04-12 18:00:002025-04-12 19:00:00
646eu-west-12025-04-12 19:00:002025-04-12 19:30:00
747us-east-12025-04-12 10:00:002025-04-12 12:00:00
848us-east-12025-04-12 10:15:002025-04-12 11:00:00

Output

server_regionpeak_concurrentpeak_at
ap-south-132025-04-12 21:30:00
eu-west-112025-04-12 18:00:00
us-east-122025-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_region

Another 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 →