Row Number - Member Retention Rahil - Workbench

set @startdate1 = '2017-07-01';
set @startdate2 = '2019-12-01';

set @row_number := 0;


(Select P.* , Q.date as date, Q.car_name as car_name, Q.dur as dur, Q.ad_sdur as ad_sdur, Q.gross_rev as Gross_rev, Q.Coupon_Spent as Coupon_Spent, Q.net_rev as net_rev , J.resv as resv from
(
### Run from here to
Select I.* from
(SELECT
@row_number:=
CASE
WHEN @L.mid = L.mid
THEN @row_number + 1
ELSE 1
END AS num,
@L.mid:= L.mid mid,L.rid
FROM
(select distinct r.member_id as mid,
r.id as rid
from reservations r
left join members m on r.member_id = m.id
where r.state in ('completed')
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid
order by mid,rid asc) L
ORDER BY L.mid,L.rid) I
where I.num < 4
#### Here
) P
left join
(select distinct A.rid2 as rid, A.mid as mid, A.return_Date as date, A.car_name as car_name, IFNULL(E.charges,0) as gross_rev,A.dur as dur, A.ad_sdur as ad_sdur,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev from
(select
distinct r.id as rid2, r.member_id as mid,cc.car_name as car_name,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
Date(r.return_at + interval '8' hour) as return_Date
from reservations r left join members m on r.member_id = m.id
left join cars cr on cr.id = r.car_id
left join car_classes cc on cc.id = cr.car_class_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 rid2) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by rid) E on A.rid2 = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid2 = F.rid
order by 2,1 asc) Q
on Q.rid = P.rid
left join
(select
distinct r.member_id as mid, count(r.id) as resv from reservations r
where r.state in ('completed')
group by r.member_id) J
on J.mid = Q.mid)