W.O.W Completed MFT Query - Rahil

set @start := '2019-1-1';
Select L.week, Count(L.mid) as mid from
(Select B.week as week, B.mid as mid from
(select distinct c.member_id as mid, weekofyear(c.created_at + interval '8' hour) as week
from charges c join members m on m.id = c.member_id
where c.kind = 'subscriptionFee'
and m.imaginary in ('normal','sofam')
and date(c.created_at + interval '8' hour) >= @start) A
join
(Select Distinct r.member_id as mid, weekofyear(r.return_at + interval '8' hour) as week
from reservations r join members m on m.id = r.member_id
where m.imaginary in ('normal','sofam')
and date(r.return_at + interval '8' hour) >= @start
and r.state in ('completed','inUse', 'reserved')
group by 1) B
on B.mid = A.mid
group by 2
order by 1 desc) L
Group by 1
order by 1 desc