Putting your user event data in sessions

I had a client whose web usage data wasn’t in sessions, so I solved it with a subquery. Here’s how I did it.

This query also enriches the data with subscribed (first use) date and the last/previous event for doing transition (clickstream) analysis.

SELECT company_id
, event_name
, date_created
, subscribed_date
, SUM(newsession) OVER (ORDER BY company_id, date_created) AS session_id
, CASE WHEN newsession = 1 THEN 'NEW_SESSION' ELSE LAG(event_name) OVER (PARTITION BY company_id ORDER BY date_created) END AS last_event
FROM
(
  SELECT company_id
  , event_name
  , date_created
  , CASE WHEN last_date IS NULL OR DATE_PART('minute', date_created - last_date) > 30 THEN 1 ELSE 0 END AS newsession
  , MIN(subscribed) OVER (PARTITION BY company_id ORDER BY date_created) AS subscribed_date
  FROM
  (
    SELECT company_id
    , event_name
    , date_created
    , lag(date_created) OVER (PARTITION BY company_id ORDER BY date_created) AS last_date
    , CASE WHEN event_name='PAID_SUBSCRIPTION_STARTED' THEN date_created END AS subscribed
    FROM events
  ) wina
) winb