Sessions from Clicks in Python

An interviewer asks: “You have a table of clicks: customer_id and clicked_at. A customer's session ends when they stop clicking for more than 30 minutes. How many sessions does each customer have?” Building sessions out of raw events is called sessionization. Product teams want sessions per customer, session length and sales per session, but clicks never arrive with a session id: the data engineer builds it, and all those numbers are only as right as that query.

The hidden setup writes clicks.csv, and the starting code loads it into the table clicks:

customer_id,clicked_at
7,2026-05-04 09:00:00
12,2026-05-04 09:05:00
7,2026-05-04 09:10:00
7,2026-05-04 09:35:00
12,2026-05-04 09:35:00
12,2026-05-04 10:10:00
7,2026-05-04 10:20:00
7,2026-05-04 10:30:00
12,2026-05-04 12:40:00
41,2026-05-04 23:50:00
41,2026-05-05 00:10:00

The trap: cutting time into fixed hours with DATE_TRUNC('hour', clicked_at) and counting each customer's hours. Here that gives 2, 3 and 2: customer 41 clicks at 23:50 and at 00:10, only 20 minutes apart, which is one session, but the clock cuts it in two. A session depends on the gap between clicks, not on the clock.

The right way takes three window columns. LAG(x) gives the value of x one row back in its window: with PARTITION BY customer_id ORDER BY clicked_at, that is the customer's previous click, and NULL for their first. Subtracting two timestamps gives an INTERVAL, a length of time you can compare with INTERVAL 30 MINUTE; a CASE turns it into a flag that is 1 when a click starts a session. A running SUM, a SUM with ORDER BY inside OVER, adds the flags up to each row, so the flags 1, 0, 1, 1, 0 become the session numbers 1, 1, 2, 3, 3. DuckDB lets a column use an alias named earlier in the same SELECT. Here is LAG on other data, a table of daily temperatures:

SELECT city, day, celsius,
    celsius - LAG(celsius) OVER (PARTITION BY city ORDER BY day) AS change
FROM temperatures

Your task: in the first query, add previous and the flag new_session inside flagged, and the running session number in the main SELECT. Then fill in the second query: each customer_id with the MAX of session as sessions, from click_sessions, one row per customer. The check prints both tables:

customer_id | clicked_at | previous | new_session | session
7 | 2026-05-04 09:00:00 | NULL | 1 | 1
7 | 2026-05-04 09:10:00 | 2026-05-04 09:00:00 | 0 | 1
7 | 2026-05-04 09:35:00 | 2026-05-04 09:10:00 | 0 | 1
7 | 2026-05-04 10:20:00 | 2026-05-04 09:35:00 | 1 | 2
7 | 2026-05-04 10:30:00 | 2026-05-04 10:20:00 | 0 | 2
12 | 2026-05-04 09:05:00 | NULL | 1 | 1
12 | 2026-05-04 09:35:00 | 2026-05-04 09:05:00 | 0 | 1
12 | 2026-05-04 10:10:00 | 2026-05-04 09:35:00 | 1 | 2
12 | 2026-05-04 12:40:00 | 2026-05-04 10:10:00 | 1 | 3
41 | 2026-05-04 23:50:00 | NULL | 1 | 1
41 | 2026-05-05 00:10:00 | 2026-05-04 23:50:00 | 0 | 1
(11 rows)
customer_id | sessions
7 | 2
12 | 3
41 | 1
(3 rows)

Assignment
Follow the Coding Tutorial to build click_sessions with LAG, a new-session flag and a running SUM, then count each customer's sessions.

Hint
A first click has no previous, so its gap is NULL. A comparison with NULL is never true, so the CASE falls to ELSE 1: the first click starts session 1. A gap of exactly 30 minutes is not over 30, so use <= for “same session”.