First Booking, Second booking by car type - Rahil

Select L.city as city, L.car_name, Round(avg(first_booking),0) as firstbooking, Round(avg(subsequent_booking),0) as subsequent_booking from
##########################carids first booking and second booking date and time gaps in between
(select A.carid as carid, A.city as city, A.car_name as car_name , A.created_date as car_created_date, B.firstbooking as first_booking_date, B.secondbooking as second_booking_date,
datediff(B.firstbooking,A.created_date) first_booking,
datediff(B.secondbooking,B.firstbooking) subsequent_booking
from
(select distinct c.id as carid, cc.car_name, z.city as city,
min(date(c.created_at + interval 8 hour )) as created_date
from cars c
left join car_classes cc on cc.id = c.car_class_id
left join zones z on c.zone_id = z.id
where c.state = 'normal'
group by 1) A
left join
(select carid as carid,
Date(temp.firstdate) as firstbooking,
(select min(date(r.return_at + interval 8 hour)) from reservations r where temp.carid = r.car_id and date(r.return_at + interval 8 hour) > temp.firstdate) as secondbooking
from
(select distinct r.car_id as carid,
min(date(r.return_at + interval 8 hour)) as firstdate,
r.id as rid
from reservations r
left join members m on m.id = r.member_id
where r.state = 'completed'
and m.imaginary in ('sofam', 'normal')
group by 1) temp) B
on A.carid = B.carid
where B.firstbooking is not null) L
where L.city in ('KL','SL','PG','JB')
group by 1,2