2,3,4 Bookings in 14 days - Rahil

################WAU, Number of customers with 2 , 3 , 4 bookings in a week (Group by date)

set @start1 := '2020-02-01';
use socar_malaysia;


select
P.min_date as min_date,
count(distinct case when P.rid = 1 then P.mid end) as 1_Booking,
count(distinct case when P.rid = 2 then P.mid end) as 2_Booking,
count(distinct case when P.rid = 3 then P.mid end) as 3_Booking,
count(distinct case when P.rid = 4 then P.mid end) as 4_Booking,
count(distinct case when P.rid = 5 then P.mid end) as 5_Booking,
count(distinct case when P.rid > 5 then P.mid end) as More_than_6_Booking

from
(Select Distinct L.mid as mid,L.name as name, L.min_date as min_date, L.age as age, count(L.rid) as rid, sum(L.dur) as Dur , Round(Avg(L.dur),2) as avg_Dur,
Round(Sum(L.gross_rev),02) as Gross_rev, Round(Sum(L.net_rev),2) as net_rev, round(Avg(L.net_rev),2) as avg_net_rev from
(select A.mid as mid,A.name as name, A.rid as rid,(A.age) as age, A.dur as dur,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, B.date as min_date, A.date as Resv_date1, A.date - B.date as diff from
(select r.id as rid, r.member_id as mid,m.display_name as name, Date(r.start_at + interval 8 hour) as Date, round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,(YEAR(r.start_at) - YEAR(m.birthday)) as age
from reservations r left join members m on m.id = r.member_id
where r.state = 'completed'
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1) A
left join
################## To find WAU, Please enter start date and it will calculate 7 days from there
(select distinct r.member_id as mid, min(r.id) as mrid,
date(r.return_at + interval 8 hour) as date
from reservations r left join members m on m.id = r.member_id
where r.state = 'completed'
and r.return_at + interval 8 hour >= @start1
and r.return_at + interval 8 hour <= date_add(@start1, interval 7 day)
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1) B
on A.mid = B.mid
############## Total spent
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')
group by rid) 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
order by 1,2 asc) L
where L.diff < 15
and L.diff > 0
group by 1) P
group by 1;

################WAU, Number of customers with 2 , 3 , 4 bookings in a week (No Grouping by date )

set @start1 := '2020-01-01';
use socar_malaysia;


select
count(distinct case when P.rid = 1 then P.mid end) as 1_Booking,
count(distinct case when P.rid = 2 then P.mid end) as 2_Booking,
count(distinct case when P.rid = 3 then P.mid end) as 3_Booking,
count(distinct case when P.rid = 4 then P.mid end) as 4_Booking,
count(distinct case when P.rid = 5 then P.mid end) as 5_Booking,
count(distinct case when P.rid > 5 then P.mid end) as More_than_6_Booking

from
(Select Distinct L.mid as mid,L.name as name, L.min_date as min_date, L.age as age, count(L.rid) as rid, sum(L.dur) as Dur , Round(Avg(L.dur),2) as avg_Dur,
Round(Sum(L.gross_rev),02) as Gross_rev, Round(Sum(L.net_rev),2) as net_rev, round(Avg(L.net_rev),2) as avg_net_rev from
(select A.mid as mid,A.name as name, A.rid as rid,(A.age) as age, A.dur as dur,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, B.date as min_date, A.date as Resv_date1, A.date - B.date as diff from
(select r.id as rid, r.member_id as mid,m.display_name as name, Date(r.start_at + interval 8 hour) as Date, round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,(YEAR(r.start_at) - YEAR(m.birthday)) as age
from reservations r left join members m on m.id = r.member_id
where r.state = 'completed'
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1) A
left join
################## To find WAU, Please enter start date and it will calculate 7 days from there
(select distinct r.member_id as mid, min(r.id) as mrid,
date(r.return_at + interval 8 hour) as date
from reservations r left join members m on m.id = r.member_id
where r.state = 'completed'
and r.return_at + interval 8 hour >= @start1
and r.return_at + interval 8 hour <= date_add(@start1, interval 7 day)
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by 1) B
on A.mid = B.mid
############## Total spent
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')
group by rid) 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
order by 1,2 asc) L
where L.diff < 15
and L.diff > 0
group by 1) P ;