Shiva Query - To Track Coupons

# Detailed promo performance
set @startDate = "2019-04-01 00:00:00";
set @dayInterval = 2;

SELECT
    reservations.id AS srid,
    reservations.member_id as mid,
    reservations.state,
    (YEAR(current_date()) - YEAR(members.birthday)) as age,
    dl.gender as gender,
    reservations.start_zone_id,
    zones.name,
    zones.region,
    zones.city,
    DATE(CONVERT_TZ(reservations.start_at,'+00:00','+8:00')) AS start_at_date,
    TIME(CONVERT_TZ(reservations.start_at, '+00:00', '+8:00')) AS start_at,
    DATE(CONVERT_TZ(reservations.end_at,'+00:00','+8:00')) AS end_at_date,
    TIME(CONVERT_TZ(reservations.end_at, '+00:00', '+8:00')) AS end_at,
    TIME(CONVERT_TZ(reservations.return_at,'+00:00','+8:00')) AS return_at,
    round((timestampdiff(minute, convert_tz(reservations.start_at, '+00:00', '+8:00'), convert_tz(reservations.end_at, '+00:00', '+8:00'))/60),2) as sdur, # plan duration
    #round((timestampdiff(minute, convert_tz(reservations.start_at, '+00:00', '+8:00'), convert_tz(reservations.return_at, '+00:00', '+8:00'))/60),1) as adur, # actual duration
    round(SUM(c.rev),2) AS rev_amount,
    round(SUM(c.rev - p.coupon),2) AS net_rev,
    round((SUM(p.coupon)/SUM(c.rev)),2) as coupon_rate
FROM
    reservations
        LEFT JOIN
    coupons ON coupons.reservation_id = reservations.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 = reservations.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 reservations.id = c.rid
        LEFT JOIN
	zones on reservations.start_zone_id = zones.id
		LEFT JOIN
	members on reservations.member_id = members.id
		LEFT JOIN (
			select driver_licenses.member_id as mid, driver_licenses.gender as gender
            from driver_licenses
            where driver_licenses.state = 'approved'
        ) as dl on reservations.member_id = dl.mid
WHERE
	reservations.state in ('completed','reserved','inUse','returnDelayed', 'canceled')
		AND coupons.coupon_policy_id = 625
        #AND coupons.comment IN ('NOMNOM')
        AND coupons.reservation_id IS NOT NULL
        AND DATE(CONVERT_TZ(reservations.created_at, '+00:00', '+8:00')) >= @startDate
        AND DATE(CONVERT_TZ(reservations.created_at, '+00:00', '+8:00')) < date_add(@startDate, interval @dayInterval month)
        # Use this to check for reservations that end before 360 minutes
        #AND timestampdiff(minute, convert_tz(reservations.start_at, '+00:00', '+8:00'), convert_tz(reservations.return_at, '+00:00', '+8:00')) < 360
        #AND timestampdiff(minute, convert_tz(reservations.return_at, '+00:00', '+8:00'), convert_tz(reservations.end_at, '+00:00', '+8:00')) >= 60
GROUP BY reservations.id
ORDER BY reservations.id desc