Card Vs Grab pay - Sign Up to MFT Conversion Rate - Mr.Hoon

set @start := '2019-05-01 00:00';
Select A.month, A.cardupload as total_cardupload, ifnull(B.Debit,0) as card, IFNULL(C.grab,0) as grappay,
P.MFTs as TotalMFT, P.card as Card_MFT, P.grabpay as grabpay_MFT
from
(select
month(pm.created_at + interval '8' hour) as month,
count(distinct pm.member_id) as cardupload
from
payment_methods as pm
where pm.state = 'approved'
and pm.created_at + interval '8' hour >= @start
group by 1) A
left Join
(select
month(pm.created_at + interval '8' hour) as month,
count(distinct pm.member_id) as debit
from
payment_methods as pm
where pm.state = 'approved'
and pm.created_at + interval '8' hour >= @start
and pm.card_type in ('debit','credit','prepaid')
group by 1) B
on A.month = B.month
left join
(select
month(pm.created_at + interval '8' hour) as month,
count(distinct pm.member_id) as grab
from
payment_methods as pm
where pm.state = 'approved'
and pm.created_at + interval '8' hour >= @start
and pm.card_type in ('eWallet')
group by 1) C
on A.month = C.month
left join
(select month(I.date) as month , Count(Distinct I.mid) as MFTs,
count(distinct case when I.paid_type in ('debit','credit','prepaid') then I.mid end) as card,
count(distinct case when I.paid_type in ('eWallet') then I.mid end) as grabpay
 from
(select A.return_date as date, A.rid2 as rid, A.mid as mid, if(rid2=firstRes,1,0) as mft, A.paid_type as paid_type  from
(select
distinct r.id as rid2, r.member_id as mid, Date(r.return_at + interval '8' hour) as return_Date, pm.card_type as paid_type,
ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes
from reservations r left join members m on r.member_id = m.id
left join payment_methods pm on pm.member_id = m.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @start
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
group by rid2) A ) I
where I.mft = '1'
group by 1) P
on P.month = A.month