MFT_23 - Rebecca

#set @start := '2019-3-1';

select
A.Date,
A.month,
#A.day,
A.TotalMFTs,
A.mft_21,
B.ActiveMembers,
B.Bookings,
B.NonCancelledBookings,
#B.hours,
CAST((A.TOtalMFTs/B.ActiveMembers)*100 AS DECIMAL(18,2)) as MFT_DAUPct
from (select
Date(c.created_at + interval '8' hour) as Date,
month(c.created_at + interval '8' hour) as month,
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 '23' 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
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(timestampdiff(hour,r.start_at, r.end_at)) as hours,
sum(case when p.state = 'normal' and p.paid_type = 'card' then p.amount end) as NetRev,
sum(case when p.state = 'normal' and p.paid_type = 'card' and str_to_date(m.birthday, '%Y%m%d') >= NOW() - interval '23' year then p.amount end) as NetRev_21,
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
;