Reservations by age - Rahil

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

from
(select
distinct r.id as rid, r.member_id as mid,
Date(r.return_at + interval '8' hour) as return_Date,
(YEAR(r.start_at) - YEAR(m.birthday)) as age
from reservations r left join members m on r.member_id = m.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid) A
group by 1
order by 1 desc