Study of Coupons - Rahil - 2 queries included

set @startdate1 = '2019-05-01';
set @startdate2 = '2019-08-01';
use socar_malaysia;
Select I.month as month,I.coupon as coupon, I.cpid as cpid, count(distinct case when I.mid is not null then I.mid end) as Mau,count(distinct case when I.rid is not null then I.rid end) as total_resv,
Sum(I.dur) as dur, Sum(I.ad_sdur) as ad_sdur, Sum(I.distance) as distance, Sum(nuse_rnd) as nuse_rnd, Sum(res_d2d) as res_d2d,sum(res_oneway) as res_oneway,
sum(gross_rev) as gross_rev,sum(Coupon_Spent) as Coupon_Spent,sum(net_rev) as net_rev,sum(mft) as mft from
(select A.month as month, A.mid as mid, A.rid2 as rid, A.coupon as coupon,A.cpid as cpid, A.dur as dur, A.ad_sdur as ad_sdur, A.distance as distance,
A.nuse_rnd as nuse_rnd,A.res_d2d as res_d2d,A.res_oneway as res_oneway ,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,if(rid2=firstRes,1,0) as mft from
(select
distinct r.id as rid2, r.member_id as mid,IFNULL(co.coupon_policy_id,"none") as cpid,IFNULL(co.comment,"none") as coupon,
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,
IFNULL(sum(ra.mileage),0) as distance,
count(if(r.way='round', r.id, null)) as nuse_rnd,
count(if(r.way='d2d', r.id, null)) as res_d2d,
count(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
Date(r.return_at + interval '8' hour) as return_Date,
Month(r.return_at + interval '8' hour) as Month,
ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes
from reservations r left join members m on r.member_id = m.id
left join coupons co on co.reservation_id = r.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed','inUse','reserved','returnDelayed','fail','canceled')
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')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
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')
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) I
where I.coupon not in ('none')
Group by I.month, I.cpid
order by month, gross_rev desc;

######## Second query just for referance

set @startdate1 = '2019-05-01';
set @startdate2 = '2019-08-01';
use socar_malaysia;
Select I.month as month,I.coupon as coupon, count(distinct case when I.mid is not null then I.mid end) as Mau,count(distinct case when I.rid is not null then I.rid end) as total_resv,
Sum(I.dur) as dur, sum(gross_rev) as gross_rev,sum(Coupon_Spent) as Coupon_Spent,sum(net_rev) as net_rev,sum(mft) as mft from
(select A.month as month, A.mid as mid, A.rid2 as rid, A.coupon as coupon,A.cpid as cpid, A.dur as dur, A.ad_sdur as ad_sdur, A.distance as distance,
A.nuse_rnd as nuse_rnd,A.res_d2d as res_d2d,A.res_oneway as res_oneway ,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,if(rid2=firstRes,1,0) as mft from
(select
distinct r.id as rid2, r.member_id as mid,IFNULL(co.coupon_policy_id,"none") as cpid,IFNULL(co.comment,"none") as coupon,
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,
IFNULL(sum(ra.mileage),0) as distance,
count(if(r.way='round', r.id, null)) as nuse_rnd,
count(if(r.way='d2d', r.id, null)) as res_d2d,
count(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
Date(r.return_at + interval '8' hour) as return_Date,
Month(r.return_at + interval '8' hour) as Month,
ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes
from reservations r left join members m on r.member_id = m.id
left join coupons co on co.reservation_id = r.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed','inUse','reserved','returnDelayed','fail','canceled')
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')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
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')
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) I
where I.coupon not in ('none')
Group by I.month, I.cpid
order by month, gross_rev desc;