# Finding the highest number of intersections in between intervals?

In SQLite, I have a fairly simple data table:

``````Periods(Start, End)
``````

I'm storing periods with start and end date (stored as integer in seconds), and I'm looking for the information of the highest number of intersections between all periods.

Consider this for easier understanding:

• Periods are guests' entry and exit time into a restaurant.
• Goal is to get the information on how many chair does the restaurant need if it want to be able to provide chairs for everyone sitting down.

Are there any elegant SQL-style solution for such a simple problem like this?

``````-- first put all the timestamps, counting +1 for every "entrance" (start)
--  and -1 for every "exit" (end)
WITH c AS
( SELECT start AS dt, +1 AS counter
FROM periods
UNION ALL
SELECT   end AS dt, -1 AS counter
FROM periods
),
-- then calculate the running totals, going through the counters
-- in order of the timestamps
cc AS
( SELECT SUM(SUM(counter)) OVER (ORDER BY dt) AS running_total
FROM cc
GROUP BY dt
)
-- then find the maximum of the running totals
SELECT MAX(running_total)
FROM cc;
``````