Loui Query for MFTs

set @startDate = "2019-04-01 00:00:00";
select uu.*, ifnull(cc.mft,0)

from(
  select z.zone_id as zid

  from car_zone_logs z

  where
  date_sub(z.log_date, interval 1 day) >= @startdate
  and date_sub(z.log_date, interval 1 day) < @startdate+interval 1 month
  and z.zone_id not in (2,3,101,383,378,697,712,818,786)

  group by zid
  order by zid desc

) uu
left join
(select zid2, count(distinct if(before_use=0, mid, null)) as mft
  from
  (select
      z.id as zid2,  r.member_id as mid,
      (select count(id)
      from reservations
      where member_id=r.member_id
      and state='completed'
      and return_at+interval 8 hour < @startdate
      and start_zone_id not in (2,3,101,383,378,697,712,818,786)) as before_use
    from reservations r, members m, zones z
    where
      r.state='completed'
      and r.member_id=m.id
      and r.start_zone_id = z.id
      and m.imaginary in ('normal', 'sofam')
      and r.return_at+interval 8 hour >= @startdate
      and r.return_at+interval 8 hour < @startdate+interval 1 month
      and r.start_zone_id not in (2,3,101,383,378,697,712,818,786)

  group by r.member_id
  ) temp

  where
    before_use=0

group by zid2) cc
on (uu.zid = cc.zid2)

group by zid
order by zid
;