Pattern

Peak concurrency

How many things were active at the same time, at most: +1 at each start, −1 at each end, then a running total.

Turn each period into two events, (start, +1) and (end, −1), with UNION ALL. Sum the events per instant first, then SUM(net) OVER (ORDER BY instant) gives how many are active after each instant; its maximum is the peak. It reads each period twice instead of joining every day with every period. Used for subscriptions, open tickets, rooms, servers.

WITH visits(arrived, left_at) AS (VALUES
  (TIME '09:00', TIME '10:30'), (TIME '09:15', TIME '09:45'),
  (TIME '09:30', TIME '11:00'), (TIME '10:30', TIME '12:00')),
changes AS (
  SELECT arrived AS moment, 1 AS delta FROM visits
  UNION ALL
  SELECT left_at, -1 FROM visits
),
per_instant AS (SELECT moment, SUM(delta) AS net FROM changes GROUP BY moment)
SELECT moment, SUM(net) OVER (ORDER BY moment) AS inside
FROM per_instant
ORDER BY moment;

moment	inside
09:00:00	1
09:15:00	2
09:30:00	3
09:45:00	2
10:30:00	2
11:00:00	1
12:00:00	0

Try it in Randori

Practise:The busiest day