'Find overlapping date-range records with Mysql 5.6?

I have a list of events with date-range and venue id. I need to find the max count of distinct active venues at the same time.

create table if not exists events
(
    eventstart datetime,
    eventend datetime,
    venue int
);

INSERT INTO events VALUES ('2022-04-06 18:00:00', '2022-04-06 19:30:00', 1);
INSERT INTO events VALUES ('2022-04-06 18:15:00', '2022-04-06 21:00:00', 1);
INSERT INTO events VALUES ('2022-04-06 18:45:00', '2022-04-06 20:00:00', 2);
INSERT INTO events VALUES ('2022-04-06 18:30:00', '2022-04-06 19:00:00', 3);

https://www.db-fiddle.com/f/a2PRXG2WQ3samrWb66fimv/4

With the previous example, I expect a result of 3 because from 18.45-19.00 there 3 concurrent distinct venues active at the same time.

I cannot find a smart way to write this query.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source