Rahil - Member- Revenue Generated per month

select
r.member_id as memberid,
m.display_name as name,
month(r.start_at + interval '8' hour) as month,
Year(r.start_at + interval '8' hour) as Year,
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 members m on m.id = r.member_id
left join
(select
p.member_id as mid, 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.member_id,month) A
on A.mid = r.member_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.member_id as mid, 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.member_id,month) B
on B.mid = r.member_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')
group by memberid,month