Query to get Day, zone, cars, Pricing and bookings

select
r.id as rid,
r.state as status,
c.id as carid,
cc.id as carclassid,
z.name as zonename,
date(r.occupy_start_at + interval '8' hour) as day,
TIMESTAMPDIFF(hour,CONVERT_TZ(r.occupy_start_at, '+00:00', '+8:00'),CONVERT_TZ(r.occupy_end_at, '+00:00', '+8:00')) as ridelength,
hour(r.occupy_start_at + interval '8' hour) as startingtime,
TIMESTAMPDIFF(minute,r.occupy_start_at + interval '8' hour,r.occupy_end_at + interval '8' hour) as ridelengthinminutes,
TIMESTAMPDIFF(day,c.created_at + interval '8' hour,'2019-3-11 00:00') as activedays,
weekday(r.occupy_start_at + interval '8' hour) as startingday,
cc.car_name as carname,
sum(p.amount) as amount,
count(distinct case when r.state = 'completed' then r.id end) as Bookings
from cars as c

join car_classes as cc
on c.car_class_id = cc.id

join zones as z
on z.id = c.zone_id

join reservations as r
on r.car_id = c.id

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

where CONVERT_TZ(r.occupy_start_at, '+00:00', '+8:00') >= '2019-1-1 00:00'
and CONVERT_TZ(r.occupy_start_at, '+00:00', '+8:00') <= '2019-1-31 00:00'

group by car_id, r.occupy_start_at + interval '8' hour,weekday(r.occupy_start_at + interval '8' hour),r.id,z.name