How to count the maximum amount of reservations that are overlapping in one day?-CodePudding

I have a table Reservations where I have a start and end date of each reservation.

id | start | end
----------------
 1     2      5
 2     4      8
 3     3      4
 4    10     15
 5     9     16

I'm trying to count the maximum amount of reservations that are overlapping in one day. So far I have a list of reservations and what other reservations are overlapping it:

SELECT r1.id, r2.id
FROM Reservations r1
JOIN Reservations r2
WHERE r2.start <= r1.end AND r2.end >= r1.start AND r1.id != r2.id

which returns

r1.id | r2.id
  1       2
  1       3
  2       1
  2       3
  3       1
  3       2
  4       5
  5       4

Now I now which reservations overlap but I don't know how to count for each day, how many reservations there are. Are there any hints how should I go on from here?

CodePudding user response：

I tried to solve the problem with a second table with the times of a day. Joining the individual IDs to every involved time and summing up the IDs over the given times. Then I choosed the maximum of theese:

DROP TABLE IF EXISTS data;

CREATE TABLE data (
    id INTEGER
    ,start INTEGER
    ,finish INTEGER
)
;

INSERT INTO data VALUES
        (1, 2, 5)
        ,(2, 4, 8)
        ,(3, 3, 4)
        ,(4, 10, 15)
        ,(5, 9, 16)
;

ANALYZE data;

DROP TABLE IF EXISTS times;

CREATE TABLE times (
        time INTEGER
)
;

INSERT INTO times VALUES
        (0)
        ,(1)
        ,(2)
        ,(3)
        ,(4)
        ,(5)
        ,(6)
        ,(7)
        ,(8)
        ,(9)
        ,(10)
        ,(11)
        ,(12)
        ,(13)
        ,(14)
        ,(15)
        ,(16)
        ,(17)
        ,(18)
        ,(19)
        ,(20)
        ,(21)
        ,(22)
        ,(23)
;

ANALYZE times;

SELECT
    MAX(d.anz_ds) max_anz_ds
FROM
    (
    SELECT
        c.time
        ,COUNT(*) anz_ds
    FROM
        (
        SELECT
            a.time
            ,b.id
        FROM
            times a
            JOIN data b ON (a.time BETWEEN b.start AND b.finish)
        ORDER BY a.time, b.id
        ) c
    GROUP BY 1
    ORDER BY 1
    ) d
;

Here is something to fiddle around with it: db<>fiddle