SU to MFT - Rahil

set @startDate = "2019-3-01 00:00:00";
#set @monthInterval = 12;

select L.*, B.signups from

(select
LEFT(YEARWEEK(created_at,3),4) AS Year,
weekofyear(created_at) as Week,
count(distinct mid) as TotMFTs,
count(distinct case when SUtoMFT <= 1 then mid end) AS "<=1",
count(distinct case when SUtoMFT > 1 and SUtoMFT <= 14 then mid end) AS "1-14",
count(distinct case when SUtoMFT > 14 and SUtoMFT <= 20 then mid end) AS "15-20",
count(distinct case when SUtoMFT > 20 and SUtoMFT <= 30 then mid end) AS "21-30",
count(distinct case when SUtoMFT > 30 and SUtoMFT <= 60 then mid end) AS "31-60",
count(distinct case when SUtoMFT > 60 and SUtoMFT <= 120 then mid end) AS "61-120",
count(distinct case when SUtoMFT > 120 and SUtoMFT <= 180 then mid end) AS "121-180",
count(distinct case when SUtoMFT > 180 and SUtoMFT <= 365 then mid end) AS "181-365",
count(distinct case when SUtoMFT > 365 and SUtoMFT <= 730 then mid end) AS "366-730",
count(distinct case when SUtoMFT > 730 then mid end) AS ">730"
from(
select
    r.id as rid,
    convert_tz(r.created_at, '+00:00', '+8:00') as created_at,
    #convert_tz(r.start_at, '+00:00', '+8:00') as start_date,
    #convert_tz(r.return_at, '+00:00', '+8:00') as return_at,
    #timestampdiff(minute, r.start_at, r.return_at)/60 as dur,
    ifnull((select min(id) from reservations where r.member_id=member_id group by member_id),0) as firstRes,
    m.id as mid,
	convert_tz(m.created_at, '+00:00', '+8:00') as signUpDate,
    timestampdiff(hour, m.created_at, r.created_at)/24 as SUtoMFT
    #str_to_date(m.birthday, '%Y%m%d') as birthdate,
    #(year(convert_tz(r.created_at, '+00:00', '+8:00'))-year(m.birthday)) as age,
    #r.way as way
from cars c, reservations r, members m, zones z
where c.id = r.car_id
    and m.id = r.member_id
    and z.id = r.start_zone_id
    and z.id not in (2,3,101)
    #and z.city in ('JB')
    and m.imaginary in ('normal')
    and convert_tz(r.created_at, '+00:00', '+8:00') >= @startDate
	#and convert_tz(r.created_at, '+00:00', '+8:00') < date_add(@startDate, interval @monthInterval month)
having rid=firstRes) A
group by Year, Week
order by Year desc, Week desc) L
left join
(select
weekofyear(m.created_at + interval '8' hour) as week,
year(m.created_at + interval '8' hour) as year,
count(distinct m.id) as signups
from members m
left outer join reservations r on
r.member_id = m.id
where m.created_at + interval '8' hour >= @startDate
and m.imaginary = 'normal'
group by 1,2) B
on L.week = B.week and L.year = B.year

group by Year, Week
order by Year desc, Week desc