Tracking MFTs from a coupon - Viny

set @startdate1 = '2019-08-01';
set @startdate2 = '2020-12-01';
use socar_malaysia;
Select distinct I.mid as mid, I.name as name, Week(I.return_date) as week, I.policy_id as policy_id , I.return_date as MFT_date, I.signup_date as signup_date , I.Coupon_Activated_date as Coupon_Activated_date,
datediff(I.return_date , I.signup_date) as Time_taken_From_Signup_To_MFT,datediff(I.return_date , I.Coupon_Activated_date) as Time_taken_From_Coupon_Activated_To_MFT
from
(select A.return_date as return_date, A.rid2 as rid,A.mid as mid, A.dur as dur, A.name as name, A.email as email, A.phone as phone, A.coupon_name as coupon_name, A.policy_id, A.signup_date as signup_date,
A.Coupon_Activated_date as Coupon_Activated_date, A.codes as codes, if(rid2=firstRes,1,0) as mft from
(select
distinct r.id as rid2, r.member_id as mid, m.display_name as name, m.email as email, ma.phone as phone, c.comment as coupon_name, c.code as codes,c.coupon_policy_id as policy_id,
ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes,
Date(m.created_at + interval '8' hour) as signup_date,
Date(c.activated_at + interval '8' hour) as Coupon_Activated_date,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
Date(r.return_at + interval '8' hour) as return_Date
from reservations r left join members m on r.member_id = m.id
left join member_appendixes ma on ma.member_id = m.id
left join coupons c on c.reservation_id = r.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid2) A) I
where I.mft = 1
and I.coupon_name like "%iprice%"
#and week(I.return_date) >= '46'
order by I.return_date asc ;