MFT by age group - Rahil

set @startdate1 = date_sub(CURDATE(), interval 6 month);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;
select A.date as date, count(A.mid) as mid,
count(distinct case when A.age >= 19 and A.age <= 23 then A.mid end) as 19_23,
count(distinct case when A.age > 23  and A.age <= 27 then A.mid end) as 24_27,
count(distinct case when A.age > 27  and A.age <= 32 then A.mid end) as 28_32,
count(distinct case when A.age > 32  and A.age <= 37 then A.mid end) as 33_37,
count(distinct case when A.age > 37  and A.age <= 42 then A.mid end) as 38_42,
count(distinct case when A.age > 42 then A.mid end) as 44_plus,
ROUND((count(distinct case when A.age >= 19 and A.age <= 23 then A.mid end)/count(A.mid))*100,2) as 19_23_PCT,
ROUND((count(distinct case when A.age > 23  and A.age <= 27 then A.mid end)/count(A.mid))*100,2) as 24_27_PCT,
ROUND((count(distinct case when A.age > 27  and A.age <= 32 then A.mid end)/count(A.mid))*100,2) as 28_32_PCT,
ROUND((count(distinct case when A.age > 32  and A.age <= 37 then A.mid end)/count(A.mid))*100,2) as 33_37_PCT,
ROUND((count(distinct case when A.age > 37  and A.age <= 42 then A.mid end)/count(A.mid))*100,2) as 38_42_PCT,
ROUND((count(distinct case when A.age > 42 then A.mid end)/count(A.mid))*100,2) as 44_plus_PCT

from
(select
distinct c.member_id as mid,
date(c.created_at + interval '8' hour) as date,
(YEAR(c.created_at) - YEAR(m.birthday)) as age
from charges c
left join members m on m.id = c.member_id
where c.created_at + interval '8' hour >= @startdate1
and c.created_at + interval '8' hour <= @startdate2
and c.kind = 'subscriptionFee'
and m.imaginary in ('sofam', 'normal')
group by 1) A
group by 1
order by 1 desc