with slots as (
WITH toDateTime('2023-01-01 09:00:00') AS start
SELECT arrayJoin(timeSlots(start, toUInt32(1200 * 3600), 3600)) stime,
date_add(hour,1,stime) etime
)
select t1.stime, sum(duration)
from
(
select stime,start_run, end_run, dateDiff(minute,start_run,end_run) as duration
from
(
select stime, etime, checkout_time, query_time,
if(stime <= checkout_time, checkout_time, stime) as start_run,
if(etime <= checkin_time, etime, checkin_time) as end_run
from dummy_runs, slots
where toRelativeHourNum(stime) >= toRelativeHourNum(checkout_time) and toRelativeHourNum(stime) <= toRelativeHourNum(checkin_time)
)
) t1
group by stime
order by stime
;