High Spent + Loyal + Festive Inactive = 3 Queries

############# High Spent users


select P.mid as mid , P.last_active_date as last_active_date, IFNULL(E.charges,0) as gross_rev,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev, Q.resv as resv ,
Round(round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) /Q.resv,2) as ABS from
(Select A.mid as mid, A.date as last_active_date from
(select
distinct r.member_id as mid,
MAX(date(r.return_at + interval '8' hour)) as date,
count(r.id) as resv
from reservations r
left join members m on m.id = r.member_id
where r.return_at + interval '8' hour >= date_sub(CURDATE(), interval 90 day)
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1
order by 1 asc) A
where A.date <= date_sub(CURDATE(), interval 15 day)) P
left join
(select c.member_id as mid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by mid) E on P.mid = E.mid
left join
(select p.member_id as mid, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.member_id) F on P.mid = F.mid
left join
(select
distinct r.member_id as mid,
count(r.id) as resv
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 asc) Q
on Q.mid = P.mid
where Q.resv >= 3
and Round(round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) /Q.resv,2) > 60

############# Loyal Inactive members


set @start := '2019-10-01';
select P.mid as mid , P.last_active_date as last_active_date, IFNULL(E.charges,0) as gross_rev,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev, Q.resv as resv ,
Round(round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) /Q.resv,2) as ABS from
(Select A.mid as mid, A.date as last_active_date from
(select distinct c.member_id as mid from coupons c
where c.coupon_policy_id  in  ('314','315', '316','317')) B
left join
(select
distinct r.member_id as mid,
MAX(date(r.return_at + interval '8' hour)) as date,
count(r.id) as resv
from reservations r
left join members m on m.id = r.member_id
where r.return_at + interval '8' hour >= date_sub(CURDATE(), interval 180 day)
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1
order by 1 asc) A
on A.mid = B.mid
where A.date <= date_sub(CURDATE(), interval 15 day)) P
left join
(select c.member_id as mid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by mid) E on P.mid = E.mid
left join
(select p.member_id as mid, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.member_id) F on P.mid = F.mid
left join
(select
distinct r.member_id as mid,
count(r.id) as resv
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 asc) Q
on Q.mid = P.mid
where Q.resv >= 3
and Round(round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) /Q.resv,2) > 60

############# Festive users

select P.mid as mid , P.last_active_date as last_active_date, IFNULL(E.charges,0) as gross_rev,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev, Q.resv as resv ,
Round(round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) /Q.resv,2) as ABS from
(Select A.mid as mid, A.date as last_active_date from
(select
distinct r.member_id as mid,
MAX(date(r.return_at + interval '8' hour)) as date,
count(r.id) as resv
from reservations r
left join members m on m.id = r.member_id
where date(r.return_at + interval '8' hour) in ('2018-12-21','2018-12-22','2018-12-23','2018-12-24','2018-12-25','2018-12-26','2018-12-27','2018-12-28',
'2018-12-29','2018-12-30','2018-12-31','2019-01-01','2019-01-02','2019-01-03','2019-01-31','2019-02-01','2019-02-02','2019-02-03','2019-02-04','2019-02-05',
'2019-02-06','2019-02-07','2019-02-08','2019-02-09','2019-02-10','2019-02-11','2019-02-12','2019-02-13','2019-06-01','2019-06-02','2019-06-03','2019-06-04','2019-06-05','2019-06-06',
'2019-06-07','2019-06-08','2019-06-09','2019-06-10','2019-08-10','2019-08-11','2019-08-13','2019-08-27','2019-08-28','2019-08-29','2019-08-30','2019-08-31','2019-09-01','
2019-09-07','2019-09-08','2019-09-09','2019-09-10','2019-09-16','2019-09-17','2019-09-18','2019-09-19','2019-09-26','2019-09-27','2019-09-28','2019-09-29')
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1
order by 1 asc) A
where A.date <= date_sub(CURDATE(), interval 15 day)) P
left join
(select c.member_id as mid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by mid) E on P.mid = E.mid
left join
(select p.member_id as mid, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.member_id) F on P.mid = F.mid
left join
(select
distinct r.member_id as mid,
count(r.id) as resv
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 asc) Q
on Q.mid = P.mid
where Q.resv >= 3
and Round(round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) /Q.resv,2) > 60