Not Working - Daily Index Query

Select
*
from

(select
distinct z.id as zoneid,
z.region as region,
z.name as zonename,
datediff(now(),z.created_at) as daysdeployed
from zones z) A

left join

(select
czl.zone_id as zoneid,
count(czl.id) as ncars
from car_zone_logs czl
where czl.zone_state='normal'
#and date_format(convert_tz(r.return_at, '+00:00', '+8:00'),'%Y-%m-%d')=date_format(czl.log_date+interval 8 hour, "%Y-%m-%d"))
group by zoneid
)B

on B.zoneid = A.zoneid

left join

(select
z.id as zoneid,
count(r.id) as nuse,
count(if(r.way='round', r.id, null)) as nuse_rnd,
count(if(r.way='d2d', r.id, null)) as res_d2d,
count(distinct r.car_id) as ncar_used,
count(distinct z.id) as nzone,
count(distinct m.id) as nmem,
#count(if(r.way='oneway', r.id, null)) as res_ow,
sum(ra.mileage) as rmil,
sum(timestampdiff(minute, r.start_at, r.end_at)/60) as dur

from zones z

left join reservations r
on r.start_zone_id = z.id

left join members m
on r.member_id = m.id

left join reservation_appendixes ra
on ra.reservation_id = r.id

group by z.id
) c

on c.zoneid = A.zoneid


###### Not workking

select

distinct z.id as zoneid,
count(distinct r.id) as nuse,
round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),0)),2) as revenues

from zones z

left join reservations r
on r.start_zone_id = z.id

left join charges ch
on ch.reservation_id = r.id

left join payments p
on p.reservation_id = r.id

where r.return_at + interval 8 hour >= '2019-01-01'
and r.return_at + interval 8 hour <= '2019-01-03'

group by zoneid