Analytical SQL with DuckDB: from clickstream to sessions

Data: both parts use synthetic data, generated locally by data/make_data.py (seeded, reproducible). Nothing is downloaded; no signup, no GPU. pip install duckdb is all the setup. Synthetic is deliberate: to score sessionization we must know the true number of sessions per user, which only generated data gives us.

Before you start

New to this? Two minutes of vocabulary, because the whole project turns on it.

DuckDB — a small analytical database that runs inside your Python process (no server to set up). Its trick: it can run SQL directly on a CSV or Parquet file, so you query the file instead of loading it into pandas first.
Aggregation — SQL’s GROUP BY: collapse many rows into per-group summaries (events per user, users per day).
Window function — a calculation that looks at other rows relative to the current one without collapsing them. LAG(x) OVER (PARTITION BY user ORDER BY time) gives the previous row’s x within each user’s timeline — here, the previous event’s time, so you can measure the gap.
Sessionization (gaps and islands) — grouping a user’s events into “sessions,” where a new session starts after a long idle gap (here, 30 minutes). It’s a classic pattern: find the gaps (idle periods), and the runs between them are the islands (sessions).

A tiny example — a user clicks at 9:00, 9:05, 9:12, then nothing until 11:30, then 11:33. With a 30-minute rule that’s two sessions (the 2.5-hour gap splits them), not five events and not one visit. Counting that correctly is the skill.

What you’ll learn: to answer analytics questions with DuckDB SQL, to use window functions, and to sessionize an event log with the gaps-and-islands pattern.

New to Python too? See Start Here — it starts from zero.

1. The Brief

You’ll point DuckDB at a raw clickstream CSV and answer the questions every product team asks — how many events and users, which actions are most common, daily active users, and (with a window function) how long between a user’s clicks — writing SQL, not loops. Then, in Part B, you’ll tackle the one that trips people up: sessionization. Given a second app’s events, you’ll group each user’s activity into sessions split by 30-minute idle gaps and count them per user, matching the true counts exactly (fraction correct 1.00) where a naive “count the events” gets it entirely wrong.

DuckDB has become the default way analysts and data engineers explore files — it’s SQLite for analytics, and “just run SQL on the CSV” is a genuinely different workflow from pandas. The reason this earns a spot at difficulty 5 isn’t the library, though; it’s the window-function reasoning. Aggregations are easy; the gaps-and-islands pattern — LAG to find the gaps, a running sum to number the islands — is the first genuinely non-obvious SQL most people meet, and it’s exactly the shape of a dozen real problems (sessions, streaks, downtime, consecutive-day logins). Learn it once here.

2. Difficulty & time

Difficulty 5 / 10. It’s about level with #hidden-communities (5, the other oss-libraries project — both use a library for a real analytical task and validate the result) and with #reorder-point-trap and #accuracy-is-lying (5). It’s clearly harder than the difficulty-3 builds (#silent-row-loss, #idempotent-loads, #benford-fraud): Part A is approachable SQL, but Part B’s gaps-and-islands sessionization is a multi-step window-function pattern that is the first genuinely non-obvious SQL most learners meet. It stays below 7–8 because it’s one tool and a well-known pattern, not an algorithm you design or a scale problem. Anchored to the rubric’s level-5 band and those named ledger projects (G10).

Time is separate from difficulty: the queries run in a fraction of a second (measured Part A wall-clock 0.23s). Part A is about 60 minutes; Part B is 2–3 hours of your own work.

3. What you’ll be able to do after

Query a CSV directly with DuckDB SQL — aggregations, group-bys, and date handling — with no server and no pandas load step.
Use window functions (LAG, OVER (PARTITION BY ... ORDER BY ...)) to reason about a row relative to its neighbours.
Sessionize an event log with the gaps-and-islands pattern, and count sessions per user.
Recognise the same pattern in its other disguises (streaks, downtime, consecutive logins).

The finished result

By the end of Part A you’ll have answered four product-analytics questions with SQL run straight on the clickstream CSV — no pandas load step:

events / users ......... 6,682 events from 200 users
most common event ...... view (3,351), then click (1,644)
peak daily active users. 200, on 2026-05-02
avg gap between clicks . 119.7 min

That ~120-minute average is pulled up by the long idle gaps between visits — and those gaps, found with LAG, are exactly what Part B turns into per-user session counts.

4. Prereqs & time box

You can write a function, and you’ve seen basic SQL (SELECT ... FROM ... WHERE ... GROUP BY). Window functions are explained here from scratch. No database setup — DuckDB is a pip install and runs in-process.

Where to write and run code

Run this project on your computer. It uses DuckDB (an in-process analytical database); the in-browser ▶ Run option isn’t confirmed for DuckDB yet, so this is a local-only project for now.

The one-time setup — installing Python, downloading the code from the course site (no GitHub, no git), opening a terminal, creating a virtual environment, installing the requirements, and running Part A step by step — is covered once in Start Here. Come back here once python -c "import duckdb" runs without error.

Time box

Part A — Guided Build: 45–75 minutes. Hard cap 90.
Part B — Your Turn: 2–3 hours. Hard cap 4.

5. Setup & data

Both datasets are synthetic, generated by one seeded command (byte-for-byte reproducible, git-ignored, never committed):

python data/make_data.py

That writes data/events_a.csv (Part A — a clickstream with user_id, event_time, event_type) and data/events_b.csv (Part B — a second app’s user_id, event_time, with built-in session structure). The true per-user session counts are documented in data/make_data.py and used by the grader — not handed to you.

6. Part A — Guided Build

Run the full script and read along, or (better) one # %% cell at a time — see Start Here (this project runs on your computer, not in the browser):

python data/make_data.py            # once
python part_a/analyze_events.py

The full, commented script is in part_a/analyze_events.py. DuckDB queries the CSV directly via read_csv_auto('events_a.csv') — no pandas load.

Step 1 — Volume.

Checkpoint: 6,682 events across 200 users.

Step 2 — Events by type.

Checkpoint: view is the most common event (3,351), then click (1,644).

Step 3 — Daily active users.

Checkpoint: peak DAU is 200, on 2026-05-02.

Step 4 — A window function: minutes between a user’s consecutive events. LAG fetches the previous event’s time within each user’s ordered timeline; date_diff('minute', ...) gives the gap.

Checkpoint: the average gap between a user’s consecutive events is ~119.7 minutes — and the big gaps are what split a user’s activity into sessions in Part B.

Why this and not that

Why DuckDB on the file, not pandas? For analytical queries — group-bys, window functions, joins across files — SQL is often clearer and DuckDB is fast on larger-than- memory files without a load step. It’s not that pandas can’t; it’s that “run SQL on the CSV” is a genuinely handy second tool, and the SQL you write here is portable to any warehouse.
Why a window function, not a self-join? You could find each event’s predecessor with a self-join, but it’s slower and error-prone. LAG ... OVER (PARTITION BY ... ORDER BY ...) says exactly what you mean — “the previous row within this user’s timeline” — and is the building block for Part B.
Why PARTITION BY user_id? Without it, LAG would compare a user’s first event to the previous user’s last event, inventing gaps across people. Partitioning keeps each user’s timeline separate — the single most common window-function mistake.

Functions you’ll meet (and where to read more)

Function	What it does here	Docs
`read_csv_auto(...)`	Query a CSV file directly, schema inferred	DuckDB CSV
`GROUP BY` / `count(DISTINCT ...)`	Volume, events-by-type, daily active users	DuckDB aggregates
`LAG(...) OVER (PARTITION BY ... ORDER BY ...)`	The previous event’s time, per user	DuckDB window functions
`date_diff('minute', a, b)`	Minutes between two timestamps	DuckDB timestamp functions

If something looks off

Your Part B session counts are almost right, but the test just misses the 0.95 floor. A new session is a gap of strictly more than 30 minutes — using >= 30, or forgetting PARTITION BY user_id so one user’s gap bleeds into the next, miscounts the boundary users. Flag a new session only on gap IS NULL OR gap > 30, per user; counting rows per user (the starter’s placeholder) isn’t almost-right — it scores near 0.
ModuleNotFoundError: No module named 'duckdb'. The install step was skipped — run pip install -r requirements.txt inside your activated virtual environment (see Start Here).
A checkpoint number doesn’t match. The data is seeded, so the numbers are exact. Re-run python data/make_data.py, then re-run Part A.

7. Part B — Your Turn

Part B is the optional “your turn” half — a guided fill-in-the-blank, not a blank page. You get a working starter (part_b/starter.py) that already handles the easy version; your job is to finish the # TODO it marks out. More open than Part A’s step-by-step, but you are never staring at an empty file. If you finished Part A and want to prove the skill, this is where it happens. New to Python? It’s completely fine to stop after Part A and come back later.

Different app, the classic hard query. data/events_b.csv is a clickstream where each user’s activity comes in bursts. A new session starts after more than 30 minutes of inactivity. Count each user’s sessions.

Same core skill as Part A (window functions over a per-user timeline), now assembled into the gaps-and-islands pattern.

Acceptance criteria (checkable)

Fill in part_b/starter.py, then:

python part_b/starter.py            # writes part_b/out/
pytest part_b/test_solution.py -q

If pytest reports everything as skipped, it just means the output files don’t exist yet — run python part_b/starter.py first (once your solution fills in the TODOs), then re-run.

Your solution writes into part_b/out/:

session_counts.csv — user_id, sessions.
report.json — users, total_sessions, fraction_users_correct, achieved_metric.

You’re done when the tests pass. The grader computes the true per-user session counts from the seed and scores the fraction of users you got exactly right — it requires ≥ 0.95. The reference scores 1.00; counting events instead of sessions scores near 0.

Constraints — what must be true, not how to get there

A new session = a gap of more than 30 minutes since the user’s previous event (the first event always starts a session).
Sessionize per user — don’t let one user’s gap bleed into another’s.
Your reported fraction_users_correct (if you fill it) must match your counts (the grader recomputes it).

Hints

Hint 1 — the gap

Reuse Part A’s window function: LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) gives the previous event’s time, and date_diff('minute', prev, event_time) the gap. The first event of each user has a null gap.

Hint 2 — gaps to islands

Flag a new session with CASE WHEN gap IS NULL OR gap > 30 THEN 1 ELSE 0 END. The number of sessions a user has is just the sum of those flags — each new-session flag starts a new island. (If you want a session id per event, a running SUM(flag) OVER (...) numbers them.)

Hint 3 — the usual mistake (still not the code)

If your counts are wrong, check the PARTITION BY user_id on the window and that you’re ordering by time. Counting rows per user (no gap logic) is the placeholder — it scores 0 because it counts events, not visits. The 30-minute rule is strictly greater-than 30.

8. Self-check

You don’t need the answer key. You’re done when:

Your Part A numbers match the checkpoints, and you can explain what PARTITION BY does.
Your Part B session counts are (almost) all exactly right, and you can explain the gaps-and-islands idea in one sentence.
You can name another problem the same pattern solves (a login streak, a run of failed jobs, downtime between alerts).

9. Stretch

Session length and depth. Extend the query to also report each session’s duration and event count, then the average across users.
Parameterise the timeout. Make the 30-minute gap a parameter and show how session counts change at 15 vs 30 vs 60 minutes — different products define a “visit” differently.
(Genuinely hard) Funnels within sessions. Within each session, detect whether the user went view → add_to_cart → purchase in order, and compute the session-level conversion rate — a window-plus-ordering problem on top of sessionization.

10. Ship it

Put this in a portfolio repo and it counts. In that repo’s README:

Lead with the one-liner an analyst gets: “I ran SQL straight on a clickstream CSV with DuckDB and sessionized it with a window function — turning raw events into per-user visits, exactly right.”
Show the DAU query and the sessionization query, and the session-count distribution.
State the transferable bit in one sentence: gaps-and-islands (LAG a gap, flag it, sum the flags) is one pattern that solves sessions, streaks, and downtime alike.

Keep make_data.py and the tests in the repo so anyone can reproduce your numbers from zero.

11. Sources

Rendered from SOURCES.md.

Field	Value
Datasets	Synthetic — clickstream events (Part A); sessioned events (Part B)
Data type	Synthetic (seeded, reproducible)
Generated by	`data/make_data.py` — `numpy.random.default_rng` (seeds 20260718 / 6161)
Source / license	Not applicable — synthetic, generated in-repo; no third-party rights
Access / verification date	2026-07-18
Redistribution	Not applicable — generator committed, CSV output never committed
robots.txt / ToS	Not applicable — nothing is scraped or fetched

The data is synthetic and teaches the mechanic (analytical SQL and sessionization); the event stream is generated with a known session structure, documented in data/make_data.py, and describes no real product.

Next up

Finished this one? Continue the Open-Source Libraries track:

Hidden communities: finding the groups inside a network and proving they’re real → · Browse all courses