Loyalty program - Initial 0.01 - Code for NUR - Rahil

select distinct A.mid,A.first_name,A.last_name, A.email,A.phone, A.resv,
Case    when A.resv < '2' then "Tier 1"
		when A.resv in (2,3) then "Tier 2"
        when A.resv in (4,5,6) then "Tier 3"
        when A.resv in (7,8,9,10) then "Tier 4"
        when A.resv in (11,12,13,14,15) then "Tier 5"
        when A.resv > 15 then "Tier 6"
END as Member_tiers
from
(select distinct r.member_id  as mid, m.first_name as first_name, m.last_name as last_name, m.email as email, ma.phone as phone, count(r.id) as resv from reservations r
left join members m on m.id = r.member_id
left join member_appendixes ma on ma.member_id = m.id
where Date(r.return_at + interval 8 hour) between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()
and r.state = 'completed'
and m.state = 'normal'
and m.imaginary in ('sofam', 'normal')
group by r.member_id
order by 6 desc) A
order by 6 desc