Grabpay Query Leon

set @start := '2019-5-22';

select
A.Day,
A.TotalMFTs,
A.GrabMFT,
A.mft_21,
B.ActiveMembers,
B.Bookings,
B.NonCancelledBookings,
B.NetRev,
B.GrabPay,
B.GrossRev,
CAST((A.TOtalMFTs/B.ActiveMembers)*100 AS DECIMAL(18,2)) as MFT_DAUPct
from (select
DAY(c.created_at + interval '8' hour) as Day,
count(distinct case when c.kind = 'subscriptionFee' and str_to_date(m.birthday, '%Y%m%d') >= NOW() - interval '21' year then c.member_id end) as mft_21,
count(distinct case when c.kind = 'subscriptionFee' and pm.card_type = 'eWallet' then c.member_id end) as GrabMFT,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) as TotalMFTs

from charges c
join members m
on m.id = c.member_id

join payment_methods pm
on pm.member_id = m.id

left outer join member_appendixes ma
on ma.id = m.id
where c.created_at + interval '8' hour >= @start
#and c.created_at + interval '8' hour <= '2018-5-2 00:00
and m.imaginary = 'normal'
group by 1) A
join (
select
DAY(r.created_at + interval '8' hour) as Day,
count(distinct case when r.state NOT IN ('canceled', 'fail') then r.member_id end) as ActiveMembers,
count(distinct case when r.state = 'completed' then r.id end) as Bookings,
count(distinct case when r.state NOT IN ('canceled', 'fail') then r.id end) as NonCancelledBookings,
count(distinct case when r.state = 'fail' then r.id end) as FailedBookings,
count(distinct case when r.state = 'fail' then r.member_id end) as UniqueFailedsids,
sum(case when p.state = 'normal' and p.paid_type IN ('card', 'eWallet') then p.amount end) as NetRev,
sum(case when p.state = 'normal' and p.paid_type IN ('eWallet') then p.amount end) as GrabPay,
sum(case when p.state = 'normal' then p.amount end) as GrossRev
from reservations r
join members m
on m.id = r.member_id and m.email not like '%socar.my%' and m.imaginary = 'normal'
join payments p
on p.reservation_id = r.id
where r.created_at + interval '8' hour >= @start
and p.state = 'normal'
group by 1
order by 1 asc) B
on B.Day = A.Day
group by 1
order by 1 asc
;