Top 50 Women Spenders

select
m.id as memberid,
dl.gender as gender,
IFNULL(round(c.rev,2),0) as Grossrev,
round((IFNULL(c.rev,0) - IFNULL(p.coupon,0)),2) as net_rev,
round(IFNULL((IFNULL(p.coupon,0)/c.rev),0),2) as coupon_rate
from reservations r
left join members m on r.member_id = m.id
left join (
        select IFNULL(payments.amount,0) as coupon, payments.reservation_id as rid
        from payments
        where payments.state = 'normal' and payments.paid_type = 'coupon'
    ) as p on p.rid = r.id

left join (
        select sum(charges.amount) as rev, charges.reservation_id as rid
        from charges
        where charges.state = 'normal' and charges.kind IN ('rent' , 'oneway', 'd2d', 'mileage')
        group by charges.reservation_id
   ) as c ON r.id = c.rid

left join driver_licenses dl on dl.member_id = m.id
where
r.state = 'completed'
and m.imaginary in ('normal', 'sofam')
and dl.gender = 'woman'
#and DATE(CONVERT_TZ(m.created_at,'+00:00','+8:00')) >= '2019-01-01'
group by m.id
order by c.rev desc
LIMIT 50