Revenue Generated per zone - Monthly - Rahil - Customer Stud

select
r.id as rid,
r.member_id as mid,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
month(r.start_at + interval '8' hour) as month,
Year(r.start_at + interval '8' hour) as Year,
r.start_zone_id as zoneid,
z.region as region,
count(r.id) as reservations,
IFNULL(round(B.charges,2),0) as gross_rev,
round((IFNULL(B.charges,0) - IFNULL(A.coupon,0)),2) as net_rev,
round(IFNULL((IFNULL(A.coupon,0)/B.charges),0),2) as coupon_rate
from reservations r
join zones z on z.id = r.start_zone_id
join members m on m.id = r.member_id
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
from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id,month) A
on A.rid = r.id and A.month = month(r.start_at + interval '8' hour) and A.year = Year(r.start_at + interval '8' hour)
left join
(select
c.reservation_id as rid, month(c.created_at + interval '8' hour) as month, Year(c.created_at + interval '8' hour) as year, sum(c.amount) as charges
from charges c
where c.state = 'normal' and c.kind IN ('rent' , 'oneway', 'd2d', 'mileage')
group by c.reservation_id,month) B
on B.rid = r.id and B.month = month(r.start_at + interval '8' hour) and B.year = Year(r.start_at + interval '8' hour)

where m.imaginary in ('normal')
and r.member_id not in ('125', '127')
and r.state in ('completed','inUse')
and r.start_at + interval 8 hour >= '2018-01-01'
group by r.id,month,zoneid
order by mid,rid