Daily Index - Signups MFT - Completed MFTs

set @start := '2020-03-01';

select
A.date,
D.signups,
C.DocUploaded,
C.DocApproved,
C.DocRejected,
A.TotalMFTs as MFT_Transaction,
T.mid as MFT_Trips,
A.mft_21,
IFNULL(O.MFT_Oneway,0) as MFT_Oneway,
IFNULL(D2.MFT_d2d,0) as MFT_d2d,
B.ActiveMembers,
B.Bookings,
round(E.Net_Rev,2) as Net_rev,
round(E.gross_rev,2) as gross_rev,
round(E.Coupon_Rev,2) as Coupon_Rev,
round((E.Coupon_Rev/E.gross_rev)*100,2) as Coupon_Rate,
CAST((A.TOtalMFTs/B.ActiveMembers)*100 AS DECIMAL(18,2)) as MFT_DAUPct,
round(E.Net_Rev/B.Bookings,2) as Netrev_perbooking
from (select
date(c.created_at + interval '8' hour) as date,
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' then c.member_id end) as TotalMFTs
from charges c
join members m
on m.id = c.member_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
date(r.return_at + interval '8' hour) as date,
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 = 'card' then p.amount end) as NetRev,
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.return_at + interval '8' hour >= @start
and p.state = 'normal'
group by 1
order by 1 asc) B
on B.date = A.date

join (select
Date(dl.created_at + interval '8' hour) as Date,
count(distinct case when dl.state not in ('noInput','null') then dl.member_id end) DocUploaded,
count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
count(distinct case when dl.state = 'reject' then dl.member_id end) DocRejected,
count(distinct case when dl.gender = 'man' then dl.member_id end) MaleApproved,
count(distinct case when dl.gender = 'woman' then dl.member_id end) FemaleApproved
from driver_licenses dl
join members m
on m.id = dl.member_id
where dl.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1) C
on C.Date = A.Date

join

(select
Date(m.created_at + interval '8' hour) as Date,
count(distinct m.id) as signups
from members m
left outer join reservations r on
r.member_id = m.id
where m.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1 ) D
on D.Date = A.Date

Join

(select
A.date as date,
A.rev as gross_rev,
B.coupon as Coupon_Rev,
A.rev - B.coupon as Net_Rev
from
(select
date(r.return_at + interval '8' hour) as date,
sum(c.amount) as rev
from charges c
join reservations r
on c.reservation_id = r.id
where c.state = 'normal' and c.kind IN ('rent' , 'oneway', 'd2d', 'mileage')
group by 1) A
join
(select
date(r.return_at + interval '8' hour) as date,
IFNULL(sum(p.amount),0) as coupon
from payments p
join reservations r on p.reservation_id = r.id
where p.state = 'normal' and p.paid_type = 'coupon'
group by 1) B
on A.date = B.date) E
on E.date = A.date
left join
(Select (A.date) as date, 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) >= '2020-01-01') A group by date order by date desc ) O
on O.date = A.date
left join
(Select L.date as date, Count(L.mid) as mid from
(Select B.date as date, B.mid as mid from
(select distinct c.member_id as mid, date(c.created_at + interval '8' hour) as date
from charges c left join members m on m.id = c.member_id
where c.kind = 'subscriptionFee'
and m.imaginary in ('normal','sofam')
and date(c.created_at + interval '8' hour) >= '2019-01-01') A
left join
(Select Distinct r.member_id as mid, date(r.return_at + interval '8' hour) as date
from reservations r left join members m on m.id = r.member_id
where m.imaginary in ('normal','sofam')
and date(r.return_at + interval '8' hour) >= '2019-01-01'
and r.state in ('completed','inUse', 'reserved')
group by 1) B
on B.mid = A.mid
group by 2
order by 1 desc) L
Group by 1
order by 1 desc) T
on T.date = A.date
left join
(Select (A.date) as date, Count(A.rid) as MFT_d2d 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 ('d2d') ) fbo on fbo.rid = r.id)FB
where date(FB.date + interval '8' hour) >= '2020-01-01') A group by date order by date desc ) D2
on D2.date = A.date
group by 1
order by 1 desc
;