Identify the customers who made a rebooking within 4 weeks

Question

For every booking on the table which is canceled. I need to check if the customer_id of the canceled booking appears with another booking within 12 weeks of the booking date.

If there exists another booking with the exact customer_id within 4 weeks after the booking date, then 1, else 0

Table 1

customer_id	booking_date	appointment_status
1	01-01-2022	canceled
2	02-01-2022	completed
3	02-01-2022	completed
4	10-01-2022	canceled
1	15-01-2022	completed
4	15-02-2022	canceled
4	17-02-2022	completed

Result

customer_id	is_rebooked	booking_date
1	1	01-01-2021
2	N/a	02-01-2022
3	N/a	02-01-2022
4	0	10-01-2022
1	N/a	15-01-2022
4	1	15-02-2022
4	N/a	17-02-2022

Since customers 1 and 4 have made a rebooking I will need to check the booking date of the next appointment is within 4 weeks if yes then 1 else 0.

Customer 2 and 3 have completed their booking therefore N/a

I would like to solve this in Mysql

Thank you

Answer 1

SELECT t1.customer_id, 
       t1.booking_date,
       CASE WHEN t1.appointment_status = 'completed'
            THEN 'N/a'
            ELSE COALESCE(SUM(t2.appointment_status = 'completed'), 0)
            END is_rebooked
FROM test t1
LEFT JOIN test t2 ON t1.customer_id = t2.customer_id
                  AND t1.booking_date < t2.booking_date
                  AND t1.booking_date + INTERVAL 15 DAY >= t2.booking_date
GROUP BY t1.customer_id, t1.booking_date, t1.appointment_status

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0b84c78f530964e627f20a5ac78c1cf4