Kusto number of overlapping intervals in a time range-CodePudding

I'm trying to write a Kusto query that needs to count how many intervals overlap for a certain date range. This is how my table looks like:

userID |  interval1         | interval2
24     |  21.1.2012 10:40   | 21.1.2012 11:00
25     |  21.1.2012 9:55    | 21.1.2012 10:50

I would like to to consider the time range given by [min(interval1), max(interval2)] with 1s step and for each instance of this interval I would like to know how many intervals from the previous table overlap. For example, for 21.1.2012 10:00 there is only one interval but for 10:45 there are two intervals overlapping.

Thank you

CodePudding user response：

Every interval1 indicates additional user's session start ( 1).
Every interval2 indicates additional user's session end (-1).
The accumulated sum indicates the number of active sessions.

let t = (datatable (userID:int,interval1:datetime,interval2:datetime)
[
     24 ,datetime(2012-01-21 10:40) ,datetime(2012-01-21 11:00)
    ,25 ,datetime(2012-01-21 09:55) ,datetime(2012-01-21 10:50)
]);
let from_dttm = datetime(2012-01-21 09:30);
let to_dttm   = datetime(2012-01-21 11:30);
let sessions_starts = (t | project delta =  1, dttm = interval1);
let sessions_ends =   (t | project delta = -1, dttm = interval2);
union sessions_starts, sessions_ends
| make-series delta = sum(delta) on dttm from from_dttm to to_dttm step 1s
| mv-apply  delta to typeof(long), dttm to typeof(datetime) on (project active_users = row_cumsum(delta), dttm)
| render timechart with (xcolumn=dttm, ycolumns=active_users)

Fiddle