Daily signups, With card_upload info

### link to access dashboard : https://docs.google.com/spreadsheets/d/1FGq9PXBEcT9UKh1sTsAcy4N36cJV6lqqHDR0CL2ZWzY/edit#gid=275227634

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

select
A.date,
D.signups,
C.DocUploaded,
C.DocApproved,
C.rejected,
F.paymentupload,
F.cardupload,
F.grabpay,
A.TotalMFTs,
A.mft_21,
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
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 like 'noInput' 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) rejected,
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(pm.created_at + interval '8' hour) as date,
count(distinct case when pm.state = 'approved' then pm.member_id end) paymentupload,
count(distinct case when pm.state = 'approved' and pm.provider in ('braintree','dummymethod','mcp') then pm.member_id end) cardupload,
count(distinct case when pm.state = 'approved' and pm.provider in ('grabpay') then pm.member_id end) grabpay
from payment_methods pm
where pm.created_at + interval '8' hour >= @start
group by 1) F
on F.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

group by 1
order by 1 desc
;