Referral Signup to MFT- Shanyi and Rahil

set @start := '2017-03-01 00:00';
set @end := '2020-03-23 00:00';
#set @end := now();

select D.Year,
    D.month,
	D.RefSignUps,
	C.RefMFTs,
	ROUND(C.RefMFTs/D.RefSignUps*100,2) AS "RefMFT/SU"

from
(select LEFT(YearWEEK(case when A.signUpDate >= @start then A.signUpDate end,3),4) AS Year,
    month(case when A.signUpDate >= @start then A.signUpDate end) as month,
	count(distinct case when A.signUpDate >= @start then A.signUpDate end) as RefSignUps
from
(select m.id as mid,
	ma.recommender as Recommender,
	convert_tz(m.created_at, '+00:00', '+8:00') as signUpDate,
	ifnull(min(r.id),0) as MFTid,
	convert_tz(r.created_at, '+00:00', '+8:00') as MFTdate
from members m
join member_appendixes ma on ma.member_id = m.id
left join reservations r on r.member_id=m.id

where m.imaginary = 'normal'
    and (m.created_at + interval 8 hour) >= @start
	and (m.created_at + interval 8 hour) < @end
    and (ma.recommender like '%@%'
    and ma.recommender not like '%activation@%'
    and ma.recommender not in ('[email protected]', '[email protected]','[email protected]',
						'[email protected]','[email protected]','[email protected]',
                        '[email protected]','[email protected]','[email protected]',
                        '[email protected]','[email protected]','[email protected]',
                        '[email protected]','[email protected]','[email protected]',
                        '[email protected]', '[email protected]', '[email protected]',
                        '[email protected]', '[email protected]', '[email protected]',
                        '[email protected]', '[email protected]', '[email protected]',
                        '[email protected]', '[email protected]', '[email protected]',
                        '[email protected]','[email protected]','[email protected]',
                        '[email protected]'))
group by 1)A group by Year, month)D

left join

(select LEFT(YearWEEK(case when B.MFTdate >= @start then B.MFTdate end,3),4) AS Year,
    month(case when B.MFTdate >= @start then B.MFTdate end) as month,
	count(distinct case when B.MFTdate >= @start then B.MFTid end) as RefMFTs
from
(select m.id as mid,
	ma.recommender as Recommender,
	convert_tz(m.created_at, '+00:00', '+8:00') as signUpDate,
	ifnull(min(r.id),0) as MFTid,
	convert_tz(r.created_at, '+00:00', '+8:00') as MFTdate
from members m
join member_appendixes ma on ma.member_id = m.id
left join reservations r on r.member_id=m.id

where m.imaginary = 'normal'
    and (m.created_at + interval 8 hour) >= @start
	and (m.created_at + interval 8 hour) < @end
    and (ma.recommender like '%@%'
    and ma.recommender not like '%activation@%'
    and ma.recommender not in ('[email protected]', '[email protected]','[email protected]',
						'[email protected]','[email protected]','[email protected]',
                        '[email protected]','[email protected]','[email protected]',
                        '[email protected]','[email protected]','[email protected]',
                        '[email protected]','[email protected]','[email protected]',
                        '[email protected]', '[email protected]', '[email protected]',
                        '[email protected]', '[email protected]', '[email protected]',
                        '[email protected]', '[email protected]', '[email protected]',
                        '[email protected]', '[email protected]', '[email protected]',
                        '[email protected]','[email protected]','[email protected]',
                        '[email protected]'))
group by 1 )B group by Year, month)C
ON D.Year = C.Year and D.month = C.month


group by Year, month
order by Year desc, month desc