Supply vs Demand Query - BY region , car and Hour

select
r.id as rid,
r.state as status,
#c.id as carid,
#cc.id as carclassid,
z.region as regionname,
date(r.occupy_start_at + interval '8' hour) as day,
TIMESTAMPDIFF(hour,CONVERT_TZ(r.start_at, '+00:00', '+8:00'),CONVERT_TZ(r.end_at, '+00:00', '+8:00')) as ridelength,
hour(r.occupy_start_at + interval '8' hour) as startingtime,
#TIMESTAMPDIFF(minute,r.start_at + interval '8' hour,r.end_at + interval '8' hour) as ridelengthinminutes,
cc.car_name as carname,
count(distinct case when r.state = 'completed' then r.id end) as Bookings,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 00:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 00:05' then r.id end) as 00am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 01:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 01:05' then r.id end) as 01am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 02:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 02:05' then r.id end) as 02am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 03:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 03:05' then r.id end) as 03am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 04:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 04:05' then r.id end) as 04am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 05:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 05:05' then r.id end) as 05am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 06:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 06:05' then r.id end) as 06am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 07:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 07:05' then r.id end) as 07am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 08:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 08:05' then r.id end) as 08am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 09:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 09:05' then r.id end) as 09am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 10:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 10:05' then r.id end) as 10am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 11:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 11:05' then r.id end) as 11am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 12:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 12:05' then r.id end) as 12am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 13:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 13:05' then r.id end) as 13am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 14:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 14:05' then r.id end) as 14am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 15:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 15:05' then r.id end) as 15am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 16:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 16:05' then r.id end) as 16am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 17:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 17:05' then r.id end) as 17am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 18:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 18:05' then r.id end) as 18am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 19:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 19:05' then r.id end) as 19am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 20:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 20:05' then r.id end) as 20am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 21:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 21:05' then r.id end) as 21am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 22:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 22:05' then r.id end) as 22am,

count(distinct case when CONVERT_TZ(r.start_at, '+00:00', '+8:00') <= '2019-1-05 23:00'
and CONVERT_TZ(r.end_at, '+00:00', '+8:00') >= '2019-1-05 23:05' then r.id end) as 23am

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.start_at, '+00:00', '+8:00') >= '2019-1-1 00:00'
and CONVERT_TZ(r.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,cc.car_name