Group page views into sessions

Hard

Question

The page_views table has one row each time a user opens a page. Group the views of each user into sessions.

A new session starts for a user when more than 30 minutes have passed since their previous view. Exactly 30 minutes still belongs to the same session. The first view of a user always starts session 1. A session can cross midnight. Times are used exactly as they are written; there are no time zones.

Return user_id, session_number (1, 2, 3 ... for each user in time order), session_start (time of the first view), session_end (time of the last view) and views (number of views in the session).

Sort by user_id, then by session_number.

The order of the rows matters for this question.

sessionization
window function
lag
running total
strftime

Read first: Lesson 49, SQL patterns for time: streaks, sessions, cohorts, funnels and rolling windows

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

page_views4 columns · 11 rowsnot run yet

CREATE TABLE page_views (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, viewed_at TEXT NOT NULL, page TEXT NOT NULL);

Example rows of the page_views table
id	user_id	viewed_at	page
1	1	2026-04-01 09:00:00	/home
2	1	2026-04-01 09:10:00	/search
3	1	2026-04-01 09:35:00	/item/7
4	1	2026-04-01 10:05:00	/cart
5	1	2026-04-01 10:40:00	/home
6	1	2026-04-01 10:50:00	/search
7	2	2026-04-01 08:00:00	/home
8	2	2026-04-01 08:31:00	/home
9	2	2026-04-01 23:50:00	/search
10	2	2026-04-02 00:10:00	/item/3
11	3	2026-04-01 12:00:00	/home

About this SQL

strftime('%s', x) gives the seconds since 1970 as TEXT, so cast it to INTEGER before you subtract (a gap of 1800 seconds is exactly 30 minutes). (julianday(a) - julianday(b)) * 24 * 60 also gives minutes, but as a decimal number with tiny errors: exactly 30 minutes can come out as 30.0000000001, so it is not safe to compare it with 30. Window functions need SQLite 3.25 or later, and you cannot nest one window function inside another, so use a CTE for each step.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready