Night Time price - Split by MFT and Query for MAU - Rahil

set @startDate = '2019-01-01 00:00:00';

select A.* , if(rid2=firstRes,1,0) as mft from
(select
DISTINCT r.id as rid2,
r.member_id as mid,
WEEKofyear(convert_tz(r.start_at, '+00:00', '+8:00')) as start_week,
DATE(convert_tz(r.start_at, '+00:00', '+8:00')) as start_date,
czl.total_cars as total_cars,
count(distinct r.car_id) as used_cars,
count(r.id) as srid,
count(distinct r.member_id) as dau,
ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes,
round(SUM(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60),2) as sdur,
round(sum(floor(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60/24)*12+if(mod(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60,24)>=12,12,mod(timestampdiff(minute, convert_tz(r.start_at, '+00:00', '+8:00'), convert_tz(r.end_at, '+00:00', '+8:00'))/60,24))),2) as adur,
round(SUM(c.rev),2) AS rev,
round(SUM(c.rev - IFNULL(p.coupon,0)),2) AS net_rev
from reservations r
left join members on r.member_id = members.id
left join coupons on r.id = coupons.reservation_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 IFNULL(SUM(charges.amount),0) 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 (select log_date, COUNT(car_zone_logs.car_id) as total_cars from car_zone_logs where car_state = 'normal' and zone_state = 'normal' group by log_date) as czl on czl.log_date = DATE(convert_tz(r.start_at,'+00:00', '+8:00'))
where   r.state = 'completed' and members.imaginary in ('sofam', 'normal')
        and convert_tz(r.start_at, '+00:00', '+8:00') >= @startDate
        # Only Monday - Thursday
        and DAYNAME(convert_tz(r.start_at, '+00:00', '+8:00')) in ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday', 'Sunday')
        # Bookings made between 9 PM to 8 AM
        and (TIME(convert_tz(r.created_at, '+00:00', '+8:00')) between '00:00:00' and '08:00:00' or TIME(convert_tz(r.created_at, '+00:00', '+8:00')) between '21:00:00' and '23:59:59')
        # Less than 12 hours
        and timestampdiff(minute, r.start_at, r.end_at)/60 < 12
group by 1
order by start_date desc) A