Key Index Dashboard - Non - Revenues - V 2.0

set @start := '2018-12-31 00:00';

select
A.Week,
A.signups,
B.DocUploaded,
B.DocApproved,
D.TotalMFTs,
C.ActiveMembers,
C.Bookings as rentals,
C.ridelength

from (select
WEEKOFYEAR(m.created_at + interval '8' hour) as Week,
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 ) A

join (select
WEEKOFYEAR(dl.created_at + interval '8' hour) as Week,
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.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) B
on B.Week = A.Week

join (select
weekofyear(r.return_at + interval '8' hour) as week,
count(distinct case when r.state = 'completed' then r.member_id end) as ActiveMembers,
count(distinct case when r.state = 'completed' then r.id end) as Bookings,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as ridelength,
sum(G.amount) as Charges
from reservations r
left outer join (select
ch.reservation_id as rid,
sum(ch.amount) as amount
from payments ch
group by 1
) G on G.rid = r.id
left join members m on m.id = r.member_id
where r.start_at + interval '8' hour >= @start
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.start_zone_id not in (2, 3, 101)
and r.state = 'completed'
group by 1) C on
C.Week = B.Week

join (
select
WEEKOFYEAR(c.created_at + interval '8' hour) as Week,
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
where c.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1) D on
D.Week = C.Week
group by 1
order by 1 asc;

Select
weekofyear(t1.date) as week,
t1.date,
    t1.ncars,
    t1.ncars - COALESCE(t2.ncars, t1.ncars) AS diff
from

(select
Date(date_sub(czl.log_date , interval 1 day)) as date,
weekday(date_sub(czl.log_date , interval 1 day)) as weekday,
count(distinct czl.car_id ) as ncars
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and weekday(date_sub(czl.log_date , interval 1 day)) = 6
group by 1) t1

left join

(select
Date(date_add(czl.log_date , interval 7 day)) as date,
weekday(date_add(czl.log_date , interval 7 day)) as weekday,
count(distinct czl.car_id ) as ncars
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and weekday(date_sub(czl.log_date , interval 1 day)) = 6
group by 1) t2

on t1.date   = t2.date - 1

order by t1.date ;


set @start2 := '2019-01-01 00:00';

select
A.month,
A.signups,
B.DocUploaded,
B.DocApproved,
D.TotalMFTs,
C.ActiveMembers,
C.Bookings as rentals,
C.ridelength
from (select
month(m.created_at + interval '8' hour) as month,
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 >= @start2
and m.imaginary = 'normal'
group by 1 ) A

join (select
month(dl.created_at + interval '8' hour) as month,
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.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 >= @start2
and m.imaginary = 'normal'
group by 1) B
on B.month = A.month

join (select
month(r.return_at + interval '8' hour) as month,
count(distinct case when r.state = 'completed' then r.member_id end) as ActiveMembers,
count(distinct case when r.state = 'completed' then r.id end) as Bookings,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as ridelength,
sum(G.amount) as Charges
from reservations r
left outer join (select
ch.reservation_id as rid,
sum(ch.amount) as amount
from payments ch
group by 1
) G on G.rid = r.id
left join members m on m.id = r.member_id
where r.start_at + interval '8' hour >= @start2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.start_zone_id not in (2, 3, 101)
and r.state = 'completed'
group by 1) C on
C.month = B.month

join (
select
month(c.created_at + interval '8' hour) as month,
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
where c.created_at + interval '8' hour >= @start2
and m.imaginary = 'normal'
group by 1) D on
D.month = C.month

group by 1

order by 1 asc;

set @start := '2018-12-31 00:00';

Select
t1.date,
    t1.ncars,
    t1.ncars - COALESCE(t2.ncars, t1.ncars) AS diff_cars,
    t1.nzones,
    t1.nzones - COALESCE(t2.nzones, t1.nzones) AS diff_zones
from

(select
Month(date_sub(czl.log_date , interval 1 day)) as date,
LAST_DAY(date_sub(czl.log_date , interval 1 day)) as weekday,
count(distinct czl.car_id ) as ncars,
count(distinct czl.zone_id ) as nzones
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and czl.log_date = LAST_DAY(date_sub(czl.log_date , interval 1 day))
group by 1) t1

left join

(select
Month(date_add(czl.log_date , interval 1 month)) as date,
LAST_DAY(date_add(czl.log_date , interval 1 month)) as weekday,
count(distinct czl.car_id ) as ncars,
count(distinct czl.zone_id ) as nzones
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and czl.log_date = LAST_DAY(date_sub(czl.log_date , interval 1 day))
group by 1) t2

on t1.date = t2.date

order by t1.date;