Calculating Concurrent Sessions, Part 2

Listing 2: Solution Based on Window Aggregate Functions

WITH Events AS

(

  SELECT app, starttime AS ts, 1 AS event_type FROM dbo.Sessions

  UNION ALL

  SELECT app, endtime, -1 FROM dbo.Sessions

),

Counts AS

(

  SELECT app,

    SUM(event_type) OVER(PARTITION BY app

                         ORDER BY ts, event_type

                         ROWS BETWEEN UNBOUNDED PRECEDING

                                  AND CURRENT ROW) AS cnt

  FROM Events

)

SELECT app, MAX(cnt) AS mx

FROM Counts

GROUP BY app;