City and age wise - MFT split - Rahil

set @start = "2019-01-01 00:00:00";
select A.week as week, A.year as year, B.city as city, count(A.mid) as totalmfts,
count(distinct case when B.age >= 19 and B.age <= 23 then A.mid end) as 19_23,
count(distinct case when B.age > 23  and B.age <= 27 then A.mid end) as 24_27,
count(distinct case when B.age > 27  and B.age <= 32 then A.mid end) as 28_32,
count(distinct case when B.age > 32  and B.age <= 37 then A.mid end) as 33_37,
count(distinct case when B.age > 37  and B.age <= 42 then A.mid end) as 38_42,
count(distinct case when B.age > 42 then A.mid end) as 44_plus,
ROUND((count(distinct case when B.age >= 19 and B.age <= 23 then A.mid end)/count(A.mid))*100,2) as 19_23_PCT,
ROUND((count(distinct case when B.age > 23  and B.age <= 27 then A.mid end)/count(A.mid))*100,2) as 24_27_PCT,
ROUND((count(distinct case when B.age > 27  and B.age <= 32 then A.mid end)/count(A.mid))*100,2) as 28_32_PCT,
ROUND((count(distinct case when B.age > 32  and B.age <= 37 then A.mid end)/count(A.mid))*100,2) as 33_37_PCT,
ROUND((count(distinct case when B.age > 37  and B.age <= 42 then A.mid end)/count(A.mid))*100,2) as 38_42_PCT,
ROUND((count(distinct case when B.age > 42 then A.mid end)/count(A.mid))*100,2) as 44_plus_PCT

from
(select distinct c.member_id as mid, weekofyear(c.created_at + interval '8' hour) as week, year(c.created_at + interval '8' hour) as year
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, (YEAR(r.start_at) - YEAR(m.birthday)) as age , z.city as city,
min(r.id) as rid
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 in ('KL','SL','PG','JB')
group by 1,2,3
order by 2 asc , 1 asc