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…