How to find maximum time range collision occurencies in Mysql-CodePudding

I have a time range entity with start and end datetime column. I need to find the maximum occurrencies (count) of overlapping the same time slot.

In the example above, the count is 4.

https://www.db-fiddle.com/f/pcq1MjQeqSEMDdyGxkFsR5/0

Probably I need some recurring query but I don't know how to start.

CodePudding user response：

For MySQL 5.x:

SELECT SUM(points2.weight) max_weight
FROM (
  SELECT start dt FROM slots
  UNION DISTINCT
  SELECT `end` FROM slots
  ) points1
JOIN (
  SELECT dt, SUM(weight) weight
  FROM (
    SELECT start dt, 1 weight FROM slots
    UNION ALL
    SELECT `end`, -1 FROM slots
    ) points
  GROUP BY dt
  ) points2 ON points1.dt >= points2.dt
GROUP BY points1.dt
ORDER BY max_weight DESC LIMIT 1

https://dbfiddle.uk/f0b56Q4X (step-by-step, with comments)