Signups to MFT - % of Conversion Ratio - Rahil

set @startdate1 = '2019-09-01';
set @startdate2 = '2020-12-01';
Select P.week , Count(distinct P.mid) as MFTs , sum( Q.resv) as resv , sum(Q.gross_rev) as Gross, Sum(Q.coupon_spent) as coupon_spent, Sum(Q.net_rev) as net_rev,
ROUND(Sum(Q.net_rev)/sum( Q.resv),2)  as Net_spent_per_resv, Round(sum( Q.resv) / Count(P.mid),2) as Resv_MFT_Ratio
from
(Select I.mid as mid, Weekofyear(I.return_date) as week
from
(select A.return_date as return_date, A.rid2 as rid,A.mid as mid, A.dur as dur, A.policy_id,
A.codes as codes, if(rid2=firstRes,1,0) as mft from
(select
distinct r.id as rid2, r.member_id as mid,  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,
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 coupons c on c.reservation_id = r.id
where r.state in ('completed','inUse','reserved')
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
group by 2
order by 1 asc) I
Where I.mft = 1
group by 1 ) P
left join
(select distinct A.mid, count(A.rid2) as resv, weekofyear(A.date) as week, sum(IFNULL(E.charges,0)) as gross_rev,
sum(IFNULL(F.coupon_Spent,0)) as Coupon_Spent,
sum(round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2)) as net_rev from
(select
distinct r.id as rid2, r.member_id as mid,
date(r.return_at + interval '8' hour) as date
from reservations r left join members m on r.member_id = m.id
where r.state in ('completed','inUse','reserved')
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')) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by rid) E on A.rid2 = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid2 = F.rid
group by 1,3
order by 3 asc , 1 asc) Q
on P.mid = Q.mid and P.week = Q.week
group by 1