Card Rejected Cases - Rahil

set @start1 := '2019-1-01 00:00';
set @start2 := NOW();
select Weekofyear(T.MFT_Date) as week,
count(distinct T.mid) as total_card_rejected_cases,
count(distinct case when T.resv = 0 then T.mid end) as Never_return,
count(distinct case when T.resv > 0 then T.mid end) as Retained
from
(Select P.date as MFT_date, P.mid as mid, IFNULL(Q.resv,0) as resv from
(select distinct I.mid as mid, I.date as date from
#### Selecting the members whose first payment status is failed
(Select A.mrid as mrid, p.paid_type as paid_type,A.way as way, A.mid as mid , A.date as date , A.state as state from
#### Finding the first Transaction Details
(select distinct r.member_id as mid, min(r.id) as mrid, date(r.start_at + interval 8 hour) as date , r.state as state, r.way as way
from reservations r left join members m on m.id = r.member_id
where m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1
order by 1 desc) A
left join payments p on p.reservation_id = A.mrid
Where A.date >= @start1
and A.date <= @start2
group by 1,2
order by 1 desc) I
where I.state = 'fail') P
#### Counting the number of Reservations
left join
(select distinct r.member_id as mid, count(r.id) as resv
from reservations r join members m on m.id = r.member_id
where m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.state in ('completed')
group by 1) Q
on P.mid = Q.mid ) T
group by 1
order by 1 desc