By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t(customer_email_hash varchar(50), booking_date date, appointment_status varchar(50));
insert into t select 'b642b4217b34b1e8d3bd915fc65c4452', '2022-01-01', 'canceled' ;
insert into t select 'a642b4217b34b1e8d3bd915fc65c4453', '2022-01-02', 'completed' ;
insert into t select 'a842b4217b34b1e8d3bd915fc65c4454', '2022-01-02', 'completed' ;
insert into t select 'z642b4217b34b1e8d3bd915fc65c4457', '2022-01-10', 'canceled' ;
insert into t select 'b642b4217b34b1e8d3bd915fc65c4452', '2022-01-15', 'completed' ;
insert into t select 'z642b4217b34b1e8d3bd915fc65c4457', '2022-02-15', 'canceled' ;
insert into t select 'z642b4217b34b1e8d3bd915fc65c4457', '2022-02-17', 'completed' ;
select customer_email_hash,
case when appointment_status='completed' then 'N/A' else
case when exists (
select * from t t2
where t2.customer_email_hash=t.customer_email_hash and t2.booking_date > t.booking_date
and t2.booking_date < Date_add(t.booking_date, interval 4 week)
) then '1' else '0' end
end Is_Rebooked,
booking_date
from t
| customer_email_hash | Is_Rebooked | booking_date |
|---|---|---|
| b642b4217b34b1e8d3bd915fc65c4452 | 1 | 2022-01-01 |
| a642b4217b34b1e8d3bd915fc65c4453 | N/A | 2022-01-02 |
| a842b4217b34b1e8d3bd915fc65c4454 | N/A | 2022-01-02 |
| z642b4217b34b1e8d3bd915fc65c4457 | 0 | 2022-01-10 |
| b642b4217b34b1e8d3bd915fc65c4452 | N/A | 2022-01-15 |
| z642b4217b34b1e8d3bd915fc65c4457 | 1 | 2022-02-15 |
| z642b4217b34b1e8d3bd915fc65c4457 | N/A | 2022-02-17 |