MFTs by City - Rahil - Count by Week and Month

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

select
A.week,
count(A.mid) as totalmfts,
IFNULL(B.city,"") as City
from
(select
distinct c.member_id as mid,
WEEKOFYEAR(c.created_at + interval '8' hour) as Week
from charges c
where c.kind = 'subscriptionFee'
and c.created_at + interval '8' hour >= @start) A
left join
(select
distinct r.member_id as mid,
min(r.id) as rid,
z.city as city
from reservations r
join members m on r.member_id = m.id
join zones z on r.start_zone_id = z.id
where m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.start_at + interval '8' hour >= @start
group by r.member_id) B
on A.mid = B.mid
where B.city is not null
group by A.week,city ;

set @start := '2019-1-1';
select
A.month,
count(A.mid) as totalmfts,
IFNULL(B.city,"") as City
from
(select
distinct c.member_id as mid,
Month(c.created_at + interval '8' hour) as month
from charges c
where c.kind = 'subscriptionFee'
and c.created_at + interval '8' hour >= @start) A
left join
(select
distinct r.member_id as mid,
min(r.id) as rid,
z.city as city
from reservations r
join members m on r.member_id = m.id
join zones z on r.start_zone_id = z.id
where m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.start_at + interval '8' hour >= @start
group by r.member_id) B
on A.mid = B.mid
where B.city is not null
group by A.month,city