Website User Session Analysis: Compute Session Metrics from Raw Clickstream Data

Question

## Problem You have a table of web events: `(user_id, page_url, event_type, timestamp)`. A **session** is a sequence of events from the same user where no two consecutive events are more than 30 minutes apart. Compute: 1. Total sessions per user 2. Average session duration per user 3. Most visited page per session ```sql -- events table: user_id VARCHAR, page_url VARCHAR, event_type VARCHAR, ts TIMESTAMP -- Step 1: identify session boundaries SELECT user_id, ts, CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS is_new_session FROM events; ``` ## Example ``` user_id | page_url | ts alice | /home | 10:00 alice | /shop | 10:15 <- same session (15 min gap) alice | /home | 11:00 <- new session (45 min gap) bob | /about | 10:05 Sessions: alice -> 2, bob -> 1 Alice session 1 duration: 15 min; session 2: 0 min ``` ## Follow-ups 1. How does this query change in a columnar store like BigQuery vs. a row store like Postgres? 2. How would you define and detect bot sessions? 3. If the events table has 10 billion rows, how do you run this efficiently?

Website User Session Analysis: Compute Session Metrics from Raw Clickstream Data

Question Details

Full Details

Topics

More from Palantir