SMM Sprint Tracker for Jason - Rahil

set @start := '2019-1-1';
Select P.month, P.GrabMFT,P.TotalMFTs, Round((P.GrabMFT/P.TotalMFTs)*100,2) as Grabpay_MFT_PCT_Total,ROUND(P.GrabPay,2) as Grabpay_Rev,Round(ROUND(P.GrabPay,2)/ROUND(P.NetRev,2)*100,2) as Grabpay_rev_PCT_Total_NetRev,
Q.MFT_Oneway,R.MFT_PCT_DIFF as MFT_PCT_DIFF
#P.mft_21, ROUND(P.NetRev,2) as Net_rev, ROUND(P.GrossRev,2) as Gross_rev,R.Diff_MFT_Oneway_MOM as Diff_MFT_Oneway_MOM
from
(select
A.month as month,A.TotalMFTs as TotalMFTs,A.GrabMFT as GrabMFT,A.mft_21 as mft_21,IFNULL(B.NetRev,0) as NetRev,IFNULL(B.GrabPay,0) as GrabPay,B.GrossRev as GrossRev
from (select
Month(c.created_at + interval '8' hour) as month,
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' and (c.created_at + interval '8' hour) >= '2019-6-12' 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 m.imaginary = 'normal'
group by 1) A
join (
select
month(r.created_at + interval '8' hour) as month,
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') and (r.start_at + interval '8' hour) >= '2019-6-12' 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.month = A.month group by 1 order by 1 asc) P
join
(Select month(A.date) as month, Count(A.rid) as MFT_Oneway from
(select FB.rid2 as rid, FB.mid as mid, FB.way as way, FB.date as date from
(select r.id as rid2 ,r.start_zone_id as stid, r.end_zone_id as edid,
date(r.start_at + interval '8' hour) as date,
fbo.mid as mid,fbo.rid,fbo.way as way from
reservations r
join
(select
fbi.mid as mid,fbi.rid as rid,fbi.way as way from
(select
distinct r.member_id as mid, min(r.id) as rid, r.way as way
from reservations r join members m on m.id = r.member_id
where r.state = 'completed' and m.imaginary = 'normal' and m.email not like '%socar.my%' group by r.member_id) fbi
where fbi.way in ('oneway','onewayReturn') ) fbo on fbo.rid = r.id
where r.start_zone_id in ('244','245') or r.end_zone_id in ('244','245') )FB
where date(FB.date + interval '8' hour) >= '2019-01-01') A group by month order by month desc ) Q
on P.month = Q.month
left join
(Select
t1.month as month,
    t1.MFT_Oneway as MFT_Oneway,
    t1.MFT_Oneway - COALESCE(t2.MFT_Oneway, t1.MFT_Oneway) AS Diff_MFT_Oneway_MOM,
    IFNULL(Round(((t1.MFT_Oneway - COALESCE(t2.MFT_Oneway, t1.MFT_Oneway))/t2.MFT_Oneway)*100,2),"") as MFT_PCT_DIFF
from

(Select month(A.date) as month, Count(A.rid) as MFT_Oneway from
(select FB.rid2 as rid, FB.mid as mid, FB.way as way, FB.date as date from
(select r.id as rid2 ,r.start_zone_id as stid, r.end_zone_id as edid,
date(r.start_at + interval '8' hour) as date,
fbo.mid as mid,fbo.rid,fbo.way as way from
reservations r
join
(select
fbi.mid as mid,fbi.rid as rid,fbi.way as way from
(select
distinct r.member_id as mid, min(r.id) as rid, r.way as way
from reservations r join members m on m.id = r.member_id
where r.state = 'completed' and m.imaginary = 'normal' and m.email not like '%socar.my%' group by r.member_id) fbi
where fbi.way in ('oneway','onewayReturn') ) fbo on fbo.rid = r.id
where r.start_zone_id in ('244','245') or r.end_zone_id in ('244','245') )FB
where date(FB.date + interval '8' hour) >= '2019-01-01') A group by month order by month desc ) t1

left join
#Month(date_add(czl.log_date , interval 1 month)) as date,
(Select month(date_add(A.date, interval 1 month)) as month, Count(A.rid) as MFT_Oneway from
(select FB.rid2 as rid, FB.mid as mid, FB.way as way, FB.date as date from
(select r.id as rid2 ,r.start_zone_id as stid, r.end_zone_id as edid,
date(r.start_at + interval '8' hour) as date,
fbo.mid as mid,fbo.rid,fbo.way as way from
reservations r
join
(select
fbi.mid as mid,fbi.rid as rid,fbi.way as way from
(select
distinct r.member_id as mid, min(r.id) as rid, r.way as way
from reservations r join members m on m.id = r.member_id
where r.state = 'completed' and m.imaginary = 'normal' and m.email not like '%socar.my%' group by r.member_id) fbi
where fbi.way in ('oneway','onewayReturn') ) fbo on fbo.rid = r.id
where r.start_zone_id in ('244','245') or r.end_zone_id in ('244','245') )FB
where date(FB.date + interval '8' hour) >= '2019-01-01') A group by month order by month desc ) t2
on t1.month = t2.month
order by t1.month desc) R
on R.month = P.month
where P.month >= Month(now() - Interval 2 month)