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