International Customers - Spend Behavior - Chi-E

select A.* , IFNULL(sum(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 , ROUND(sum(E.charges)/A.resv,2) as gross_basket_size, ROUND((IFNULL(E.charges,2) - IFNULL(F.coupon_Spent,0))/A.resv,2)  as Net_basketsize from
(select distinct r.member_id  as mid,m.display_name as name, r.start_zone_id as start_zone_id, z.region as region, ma.country as country,
count(r.id) as resv
from reservations r left join members m on m.id = r.member_id
left join zones z on z.id = r.start_zone_id
left join member_appendixes ma on ma.member_id = r.member_id
where m.imaginary in ('sofam', 'normal')
and m.state = 'normal'
    and r.member_id not in ('125', '127')
    and z.id not in (2, 3, 101)
    and r.state in ('completed')
    and ma.country not like 'MYS'
group by 1) A
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')
group by 1) E on A.mid = E.mid
left join
(select p.member_id as mid, IFNULL(sum(p.amount),0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by 1) F on A.mid = F.mid
group by 1