1

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?

enter image description here

1 Answer 1

3
-- 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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.