MySQL - Classic Date Functions

Question

Version: 10.2.18-MariaDB

I'm attempting to find the first available room within December (already included in the sample table) that will be free for 3 consecutive days.

The expected output should be:

Room 201, 2016-12-11

My question is essentially more about the syntax of SQL, should I be using the DATEDIFF function to determine the booking end date, or is there another method which I am not thinking of?

+--------------+--------+---------+--------------+
| booking_date | nights | room_no | arrival_time |
+--------------+--------+---------+--------------+
| 2016-12-01   |      2 |     201 | 17:00        |
| 2016-12-02   |      2 |     301 | 20:00        |
| 2016-12-03   |      5 |     101 | 16:00        |
| 2016-12-03   |      4 |     201 | 18:00        |
| 2016-12-04   |      1 |     301 | 15:00        |
| 2016-12-05   |      5 |     301 | 14:00        |
| 2016-12-07   |      4 |     201 | 20:00        |
| 2016-12-08   |      2 |     101 | 18:00        |
| 2016-12-10   |      5 |     101 | 23:00        |
| 2016-12-12   |      1 |     301 | 12:00        |
| 2016-12-15   |      3 |     101 | 19:00        |
+--------------+--------+---------+--------------+

Answer 1

Test:

WITH cte AS (
SELECT room_no, 
       booking_date + INTERVAL nights DAY d_start, 
       DATEDIFF(COALESCE(LEAD(booking_date) OVER (PARTITION BY room_no ORDER BY booking_date), 
                         '2100-01-01'), 
                booking_date + INTERVAL nights DAY) d_free
FROM booking
)
SELECT room_no, d_start
FROM cte
WHERE d_free >= 3
ORDER BY 2;

Output on test data is:

room_no d_start
201     2016-12-11
301     2016-12-13
101     2016-12-18

fiddle