MFT MAU ACTIVE MEMBERS

select

A.mid,
A.rid,
A.week,
A.ActiveMembers,
B.carname,
B.Regionname,
B.amount

from


(select
m.id as mid,
r.id as rid,
week(r.occupy_start_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

from reservations r
join members m
on r.member_id = m.id

where r.start_at + interval '8' hour >= '2019-3-1 00:00'
and r.state = 'completed'
and m.imaginary = 'normal'
and m.state = 'Normal'

Group by rid) A

left join

(select
distinct m.id as mid,
r.id as rid,
min(week(r.start_at + interval '8' hour)) as week,
c.id as carid,
cc.car_name as carname,
z.id as zoneid,
z.region as regionname,
(p.amount) as amount
from reservations r

left join members m
on m.id = r.member_id

left join cars c
on c.id = r.car_id

left join car_classes cc
on cc.id = c.car_class_id

left join zones z
on z.id = c.zone_id

join payments p
on p.reservation_id = r.id

where r.start_at + interval '8' hour >= '2019-3-1 00:00'
#and r.state = 'completed'

group by mid) B

on A.mid = B.mid