How best to compute session length by aggregation interval?

[Jay Lim]

Say, I have 2 sessions:

User A from 2023-01-01 09:00 to 2023-01-01 12:00

User B from 2023-01-01 10:30 to 2023-01-01 13:00

Let's say we want to show the duration by the hour (could be by 5min, 1/2hr, 1hr, 1 day etc) and the slot 9:00 refers to any usage between 9-10. 10:00 is between 10-11 etc.

The desired output:

2023-01-01: 09:00 = 1hr

2023-01-01: 10:00 = 1.5hr

2023-01-01: 11:00 = 2hr

2023-01-01: 12:00 = 1hr

Right now i build a slot table covering a start/end of 1hr each then checking and adjusting the actual sessions to a new start/end date. Something like below. But i think there's must be a quicker/simpler way?

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

;