Rentals - SYNC - LOUI - Key Index Dashboard

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

select
A.month,
C.Bookings as rentals

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 >= @start
and m.imaginary = 'normal'
group by 1 ) A

 #### Replace the Code with present code to SYNC Numbers with loui
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,
sum(timestampdiff(minute,CONVERT_TZ(r.start_at, '+00:00', '+8:00'), CONVERT_TZ(r.end_at, '+00:00', '+8:00'))/60) 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
join members m on m.id = r.member_id
where r.return_at + interval '8' hour >= @start
and m.imaginary in  ('normal','sofam')
and r.state = 'completed'
and r.start_zone_id not in (2,3,378,712)
group by 1) C on
C.month = A.month

group by 1

order by 1 asc;