Signups by age group - Shanyi

set @startDate = "2019-1-01 00:00:00";

select
#LEFT(YEARWEEK(created_at,3),4) AS Years,
year(created_at) as Years,
month(created_at) as month,
weekofyear(created_at) as week,
count(distinct mid) as TotSignUps,
count(distinct case when su_age >= 19 and su_age <= 23 then mid end) AS "19-23",
count(distinct case when su_age > 23 and su_age <= 28 then mid end) AS "24-28",
count(distinct case when su_age > 24 and su_age <= 33 then mid end) AS "29-33",
count(distinct case when su_age > 33 and su_age <= 38 then mid end) AS "34-38",
count(distinct case when su_age > 39 and su_age <= 43 then mid end) AS "39-43",
count(distinct case when su_age > 43 and su_age <= 48 then mid end) AS "44-48",
count(distinct case when su_age > 48 and su_age <= 53 then mid end) AS "49-53",
count(distinct case when su_age > 53 and su_age <= 58 then mid end) AS "54-58",
count(distinct case when su_age > 58 and su_age <= 63 then mid end) AS "59-63",
count(distinct case when su_age > 63 then mid end) AS ">63"

from(
select m.id as mid, m.created_at, dayname(m.created_at) as day, m.birthday,
    (year(convert_tz(m.created_at, '+00:00', '+8:00'))-year(m.birthday)) as su_age
from members m
)A

group by Years, month, week
order by Years desc, month desc , week desc