Polars Analytics: window functions for the questions group-by can't answer

Project at a glance

• Track: Open-Source Libraries
• Difficulty: Intermediate · 5/10
• Time: ~1.0 h for Part A · ~2.5 h more for the optional Part B
• You’ll use: polars, pandas, numpy, pytest
• 💻 Local setup needed (no in-browser ▶ Run yet) — a one-time, free Python install, then you just run files. Start Here walks you through it.
• New here? This is an intermediate project — it assumes you can read and run a little Python. New to the setup? Start Here walks you through it.

The data in this project is synthetic — generated locally by data/make_data.py, with no external source. That is deliberate: grading sessionization needs each user’s true session count, which a real clickstream never labels, and the sessions are generated with controlled gaps so the 30-minute rule recovers them exactly. No signup, no downloads, no GPU.

Before you start

New to polars or window functions? Start here.

Polars is a fast DataFrame library. Two ideas matter here:

• Lazy frames. pl.scan_csv(...) doesn’t read the file — it builds a plan. You chain filters, selects, and aggregations, and .collect() runs the whole thing at once, so polars can optimise it (read only the columns and rows you actually use).
• Window functions — expr.over("user_id"). A group_by collapses each group to one row. A window keeps every row and attaches a per-group value beside it. That’s the only way to ask “what’s this event’s share of this user’s total?” or “what’s the running total for this user, in time order?” — questions where you need the group answer and the original rows.

A tiny example — running total per user, in time order:

user  time   spend    cum_spend (over user, ordered by time)
A     09:00   10       10
A     09:20   30       40
B     09:05   50       50      <- B's window is separate from A's
A     10:00   20       60

cum_spend is spend.cum_sum().over("user") on time-sorted rows. Compute it with a global cumulative sum instead and you’d add B’s spend into A’s running total — nonsense. The window is what says “within this user, in this order.”

What you’ll learn: use polars’ lazy API and window functions to compute per-user, order-dependent metrics — and see why a global operation gives the wrong answer. Section 3 lists exactly what you’ll be able to do.

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

1. The Brief

You’ve got a raw clickstream — millions of user events, unsorted — and the questions that matter are all “per user, over time”: how much has each user spent to date, and how many separate visits (sessions) did it take. You’ll answer them with polars window functions, and see first-hand why the naive row-by-row approach quietly produces garbage on an interleaved log.

Window functions are the workhorse of analytics SQL and modern DataFrame libraries — running totals, ranks within a group, gaps between events, sessionisation. Polars makes them fast and explicit, and the “partition and order” discipline you learn here is the same in SQL’s OVER (PARTITION BY ... ORDER BY ...).

2. Difficulty & time

Difficulty 5 / 10. A new library (polars’ lazy + expression API) and one genuinely error-prone idea: an ordered window. Calibrated against the ledger (G10): a peer of analytics-with-duckdb (5) — learn a query engine and apply it to a real analytical question — and of reorder-point-trap (5) and mtouch-attribution (5), which likewise turn on one correct modelling choice. It sits below vendor-dedupe (6), which layers blocking and fuzzy matching. The trap — using a global diff instead of a per-user ordered one — silently returns a plausible but wrong answer, which is what pushes it above the 3–4 band.

Time is separate from difficulty: Part A is about an hour; Part B is 2–3 hours. The queries run in a fraction of a second.

3. What you’ll be able to do after

• Use polars’ lazy API — scan_csv, expressions, group_by().agg(), collect() — and read a query plan.
• Write window functions with .over() — a per-group aggregate broadcast back to rows.
• Write an ordered window — cumulative sums and gaps within a group, in time order.
• Sessionise a clickstream — and explain why a global (unpartitioned) diff gets it wrong.

The finished result

By the end of Part A you’ll have taken a 6,560-event clickstream and answered “per user, over time” questions a plain group_by can’t — keeping every row while attaching each user’s totals:

events & users ......... 6,560 events across 400 users
revenue ................ 558 purchases hold all $112,155; all other events are $0
share window ........... every row kept, with its user's total and its share beside it
running window ......... each user's spend-to-date, in time order (an ordered window)
top spender ............ U00206 at $1,287  (written to part_a/user_spend_a.csv)

Same rows kept throughout — that’s what a window buys you over a group-by. In Part B you carry the ordered window to time and sessionise the clickstream with a 30-minute-gap rule, per user.

4. Prereqs & time box

You can write a function and have used a DataFrame (pandas is fine — polars is similar). No polars experience needed; Part A introduces it. No prior window-function knowledge required.

Where to write and run code

Run this project on your computer. It uses Polars (a fast DataFrame library); the in-browser ▶ Run option isn’t confirmed for Polars 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 polars" runs without error.

Time box

• Part A — Guided Build: 60–90 minutes. Hard cap.
• Part B — Your Turn: 2–4 hours. Hard cap.

5. Setup & data

The data is synthetic and generated locally — see the banner above and SOURCES.md for full provenance. One command creates both parts’ data:

python data/make_data.py

That writes two CSVs into data/ (git-ignored; regenerate anytime, byte-for-byte identical because the seed is fixed):

• Part A — events_a.csv (6,560 events, 400 users; user_id, timestamp, event_type, revenue).
• Part B — events_b.csv (24,198 events, 600 users; same columns, generated with a known session structure). Rows are shuffled — the log is not pre-sorted.

6. Part A — Guided Build

You’ll learn the lazy API and windows on the clickstream. Run the whole thing, or (better) one # %% cell at a time — see Start Here (this project runs on your computer, not in the browser):

python part_a/windows.py

The full, commented script is in part_a/windows.py. The spine:

Step 1 — Lazy scan + expressions. scan_csv, group_by().agg(), collect().

Checkpoint: 6,560 events, 400 users; purchases hold all the revenue, other event types $0.

Step 2 — A window: share of the user’s total. col("revenue") / col("revenue").sum().over("user_id").

Checkpoint: every row keeps its detail and gains its user’s total and the row’s share — no collapse. (A group_by would have reduced each user to one row.)

Step 3 — An ordered window: running revenue per user. col("revenue").cum_sum().over("user_id") on time-sorted rows.

Checkpoint: each user’s spend-to-date climbs in time order; the window carries the “within this user, in this order” meaning.

Step 4 — The query plan. explain() shows projection + predicate pushdown.

Checkpoint: the plan pushes the purchase filter and the column selection down to the scan. Writes part_a/user_spend_a.csv (top spender ≈ $1,287).

Why this and not that

• Why a window, not a group-by. A group-by answers “one number per user”; a window answers “for each row, given its user.” Share-of-total and running totals need the rows kept, so they’re window territory.
• Why order matters in a window. cum_sum().over("user_id") is only meaningful once the rows are sorted by time within the user. Ordered windows (cumulative, lag, rank) are wrong on unordered data — and Part B’s whole task is an ordered window.
• Why lazy. Describing the query as a plan lets polars read only the columns and rows it needs and fuse the steps into one pass. On a big log that’s the difference between fitting in memory and not.

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

Function	What it does here	Docs
pl.scan_csv(path)	Build a lazy plan over the file	scan_csv
expr.over("user_id")	Window: per-group value, rows kept	Expr.over
expr.cum_sum()	Cumulative sum (ordered window)	cum_sum
expr.diff()	Difference from the previous row	diff
LazyFrame.explain()	Show the optimised query plan	explain

If something looks off

• You ran a cell and nothing printed. A lazy frame — pl.scan_csv(...) and the expressions you chain onto it — doesn’t run until you call .collect() (or .explain()); until then it’s only a query plan, so a line like lf = pl.scan_csv(...) produces no output. That’s expected, not a broken cell: run the step that ends in .collect()/.explain() and the results appear. Already eager frames (pl.read_csv, or one you’ve collected) print right away.
• ModuleNotFoundError: No module named 'polars'. 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.

Same core skill, applied to time. Sessionise events_b.csv: count each user’s sessions, where a new session begins when the gap since that user’s previous event exceeds 30 minutes. The starter takes the gap with a global diff across the whole time-sorted frame and scores ~0 — the log interleaves users, so consecutive rows are different people seconds apart.

The transfer: Part A’s share window only partitioned by user; sessionisation needs a window that is partitioned and ordered — the gap since this user’s previous event, in time order — then a running count of the gaps over the threshold.

Acceptance criteria (checkable)

Your solution writes into part_b/out/:

• sessions.csv — columns user_id, session_count, one row per user.
• report.json — with keys users, total_sessions, achieved_metric (you can leave achieved_metric null; the grader scores you).

Then:

python part_b/starter.py
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.

You’re done when the tests pass: ≥ 90% of users’ session counts match the truth (the reference gets 100%). The global-diff starter matches ~0% — the per-user ordered window is what clears the floor.

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

• Take the gap per user, in time order — sort by ["user_id", "timestamp"] and use .diff().over("user_id").
• A new session starts on the first event and whenever the gap exceeds 30 minutes.
• Count sessions per user; output one row per user.
• Don’t assume the input is sorted — it isn’t.

Hints

Hint 1 — the per-user gap

Sort by ["user_id", "timestamp"], then pl.col("timestamp").diff().over("user_id") gives the time since each user’s previous event (null for their first). Compare it to pl.duration(minutes=30).

Hint 2 — mark new sessions

A row starts a new session if its gap is null (first event) or greater than 30 minutes. Cast that boolean to an int so you can sum it.

Hint 3 — count per user (still not the code)

The session count for a user is just the number of “new session” rows they have — group_by("user_id").agg(pl.col("new").sum()).

8. Self-check

You don’t need the answer key. You’re done when all of these hold:

• pytest reports accuracy ≥ 0.90 (aim for 1.00 — the gap rule recovers the sessions exactly).
• Your total session count is in the low thousands, not a handful; a global diff collapses almost everything into one session per stretch and gives an absurdly low total.
• Re-running from python data/make_data.py reproduces the same numbers — the data is seeded.

And the tell-tale: if you don’t partition the diff by user, your session total will be tiny (a few) — the sign that the window isn’t scoped to each user.

9. Stretch

• Session-level metrics. Assign a session id to each event (cum_sum of the new-session flag, over the user) and compute per-session duration, event count, and revenue.
• Tune the timeout. Recompute session counts for 15- and 60-minute gaps and show how the definition changes the answer — sessionisation is a modelling choice, not a fact.
• (Genuinely hard) Lazy end-to-end. Rewrite the whole pipeline as a single LazyFrame chain (no intermediate collect) and read the query plan to confirm the sort and window are fused.

10. Ship it

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

• State the finding in one sentence: “sessionised a 24k-event clickstream with polars window functions — X sessions across 600 users — and showed why a global diff undercounts by 1000×.”
• Show the per-user ordered window (the .diff().over(...)) next to the wrong global version, with both session totals.
• Include one paragraph on window functions: partition and order, and why order-dependent metrics break without it. That paragraph is what signals you understand windows, not just polars syntax.

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
Dataset	Synthetic e-commerce clickstream (Part A 6,560 events/400 users; Part B 24,198 events/600 users)
Type	Synthetic
Generated by	data/make_data.py (this repo), seeds 20260734 (A) and 20260735 (B)
Source URL	Not applicable — generated, not fetched
License	Not applicable — synthetic, no third-party rights
Access / generation date	2026-07-18
Redistribution	Permitted — synthetic; output regenerated from the seeded script, never committed
robots.txt checked	Not applicable — no scraping
ToS URL	Not applicable — no external source

Synthetic data teaches the mechanic; it does not prove a finding about the real world. The session structure was generated on purpose — recovering it demonstrates the technique, not a claim about any real store’s traffic.

Next up

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

Fraud Rings: the collusion you can only see in the graph →  ·  Browse all courses