Drill 12 of 12 in Gaming questions
0

Interview Question Bank · Event sweep · running SUM · tie order at the same instant

12. Peak concurrent players per region

HardGamingQuestion #156

Concept: Event sweep · running SUM · tie order at the same instantshow

To find the most intervals open at once, turn each interval into two events, +1 at its start and -1 at its end, and take a running SUM in time order: the running total is how many are open at that moment, and its maximum is the peak. When an end and a start share a timestamp and ends are exclusive, process the -1 first (ORDER BY time, delta), or you count a moment of overlap that never happened.

Example (different data):

WITH ev AS (
  SELECT check_in AS at, 1 AS delta FROM hotel_stays
  UNION ALL
  SELECT check_out, -1 FROM hotel_stays
)
SELECT at, SUM(delta) OVER (ORDER BY at, delta ROWS UNBOUNDED PRECEDING) AS occupied
FROM ev
ORDER BY occupied DESC, at
LIMIT 1;

Your task

Skyrift Arena's server team sizes each region's game servers by peak load. They want the highest number of players online at the same moment on 2025-01-25, the evening of a big tournament.

Sessions overlap when one starts before another ends. ended_at is exclusive, so a session ending at 20:00 doesn't overlap one starting at 20:00. For each region, return region, peak_players (the most sessions running at the same moment) and peak_at (the earliest moment that peak was reached). Sort by region.

Starting Postgres in your browser…

Checking your access…