Premium SCDW Customers - Behaviour

set @start := now() - interval 3 month;

Select P.mid as mid, P.name as name, P.age as age, P.Prem_insurance_resv as Prem_insurance_resv, P.amount as SCDW_Resv_amount, P.Premium_SCDW_amount as Premium_SCDW_Paid, P.dur as SCDW_dur,
Q.resv as Resv_90days, Q.gross_rev as gross_rev_90days, Q.Coupon_Spent as Coupon_Spent_90days,Q.net_rev as net_rev_90days, Q.dur as dur  from

###### Finding Premium SCDW Customers

(Select A.mid as mid, count(distinct case when A.Premium_SCDW = 1 then A.rid end) as Prem_insurance_resv, SUM(A.Premium_SCDW_amount) as Premium_SCDW_amount , SUM(A.dur) as dur , Sum(A.amount) as amount,
A.name as name , A.age as age from
(select distinct r.id as rid, m.id as mid,m.display_name as name, (YEAR(r.start_at) - YEAR(m.birthday)) as age, round((timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur, sum(c.amount) as amount,
count(distinct case when cm.meta LIKE '%Basic SCDW%' then cm.charge_id end) as Basic_SCDW,
count(distinct case when cm.meta LIKE '%Standard SCDW%' then cm.charge_id end) as Standard_SCDW,
count(distinct case when cm.meta LIKE '%Premium SCDW%' then cm.charge_id end) as Premium_SCDW,
ROUND(ifnull(Sum(distinct case when cm.meta LIKE '%Premium SCDW%' then c.amount end),0),2) as Premium_SCDW_amount
from reservations r
left join charges c on c.reservation_id = r.id
left join members m on m.id = c.member_id
left join charge_meta  cm on cm.charge_id = c.id
where m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.state in ('completed','inUse')
and Date(r.start_at + interval 8 hour) >= @start
and c.state = 'normal'
group by 1) A
where A.Premium_SCDW = 1
group by 1
order by 2 desc) P

left join

###### Mapping with all members last 30 days Reservations

(select  A.mid as mid, A.name as name,A.age as age, Count(A.rid) as resv, sum(A.dur) as dur, IFNULL(SUM(E.charges),0) as gross_rev,
IFNULL(SUM(F.coupon_Spent),0) as Coupon_Spent,
round((IFNULL(SUM(E.charges),0) - IFNULL(SUM(F.coupon_Spent),0)),2) as net_rev from
(select distinct r.id as rid, r.member_id  as mid, m.display_name as name, (YEAR(r.start_at) - YEAR(m.birthday)) as age, round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur
from reservations r left join members m on m.id = r.member_id
left join zones z on z.id = r.start_zone_id
left join member_appendixes ma on ma.member_id = r.member_id
where m.imaginary in ('sofam', 'normal')
and m.state = 'normal'
and r.member_id not in ('125', '127')
and z.id not in (2, 3, 101)
and r.state in ('completed', 'inuse')
and Date(r.start_at + interval 8 hour) >= @start
group by 1) 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 c.reservation_id) E on A.rid = 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.rid = F.rid
group by 1
order by 1 asc) Q
on Q.mid = P.mid
order by P.mid asc