Sessionization with Temporal Union

A common problem in web analytics is sessionization or session reconstruction: taking a series of user events and splitting the series into a set of sessions [1]. Often, it is done based solely on the timestamps of the events: a session ends after a certain time of user inactivity. As an example, let's take the following click stream:

userIdpagetimestamp
0001"index.html"2015-05-10 20:13:13
0001"search.html"2015-05-10 20:13:20
0001"search.html"2015-05-10 20:19:01
0001"checkout.html"2015-05-10 20:25:20
0002"index.html"2015-05-10 21:03:31
0002"search.html"2015-05-10 21:04:20
0001"index.html"2015-05-10 24:25:25
0001"search.html"2015-05-10 24:25:30

If we assume that a session ends after at least 15 minutes of user inactivity, the sessionized version of this data would look like this:

userIdsession_beginsession_end
00012015-05-10 20:13:132015-05-10 20:25:20
00022015-05-10 21:03:312015-05-10 21:04:20
00012015-05-10 24:25:252015-05-10 24:25:30

So how would you solve it in SQL? The solution to a similar problem, Packing date and time intervals, was posted here. Packing of intervals means that you want to merge all intervals that overlap into one contiguous interval problem. We can transform our original sessionization problem into interval-packing problem by turning every click event into a 15-minute interval. If two such intervals overlap, it means that the original click events were less than 15 minutes apart. So packing such intervals, and then substracting 15 minutes from each row in the result, will give us the sessions. Here is an SQL query that does that:

WITH ExtendedClicks AS
(
  SELECT userId, timestamp as starttime, DATEADD(MINUTE, 15, timestamp) as endtime
  FROM Clicks
),
C1 AS
(
  SELECT userId, starttime AS ts, +1 AS type, NULL AS e,
    ROW_NUMBER() OVER(PARTITION BY userId ORDER BY starttime) AS s
  FROM ExtendedClicks

  UNION ALL

  SELECT userId, endtime AS ts, -1 AS type,
    ROW_NUMBER() OVER(PARTITION BY userId ORDER BY endtime) AS e,
    NULL AS s
  FROM ExtendedClicks
),
C2 AS
(
  SELECT C1.*, ROW_NUMBER() OVER(PARTITION BY userId ORDER BY ts, type DESC) AS se
  FROM C1
),
C3 AS
(
  SELECT userId, ts,
    FLOOR((ROW_NUMBER() OVER(PARTITION BY userId ORDER BY ts) - 1) / 2 + 1) AS grpnum
  FROM C2
  WHERE COALESCE(s - (se - s) - 1, (se - e) - e) = 0
),
ExtendedSessions AS
(
  SELECT userId, MIN(ts) AS starttime, MAX(ts) AS endtime
  FROM C3
  GROUP BY userId, grpnum;
)
SELECT userID, starttime, DATEADD(MINUTE, -15, endtime) as endtime
FROM ExtendedSessions

In Rax, we introduced the temporal union operator, @\/ which solves the interval-packing problem in one line of code. The sessionization problem in Rax:

{[#:userId, $:page, |:interval]}: ExtendedClicks :=
  project [.userId, .page, (|)[.timestamp, .timestamp + (^)"PT15M"]]
  Clicks;

{[#:userId, |:interval]}: ExtendedSessions :=
  project [.userId, .#1]
  @\/:[.userId] ExtendedClicks;

{[#:userId, |:interval]}: Sessions :=
  project [.userId, (|)[.interval.begin, .interval.end - (^)"PT15M"]]
  ExtendedSessions;

Which code do you prefer?