GROUP BY collapses rows into one per group. Sometimes you want the opposite: keep every row,
and add a column computed across a group — a rank inside a category, a running total month
by month. That's a window function, and this level writes two of them.
Goal
Two SQL nodes over the same orders and products: one ranks products by revenue inside
their category, the other adds a running total of revenue by month.
You'll need
- Level 3.2's files:
orders.csvandproducts.csv. - About 20 minutes.
Steps
1. Rank inside each category
Create a pipeline w3-4-windows with orders and products sources. Add a SQL node
rank_in_category, connecting orders first (input1) and products second (input2):
SELECT p.category, p.product,
SUM(o.quantity * p.list_price) AS revenue,
RANK() OVER (
PARTITION BY p.category
ORDER BY SUM(o.quantity * p.list_price) DESC
) AS rank_in_category
FROM input1 AS o -- orders
JOIN input2 AS p ON p.sku = o.sku -- products
WHERE o.status = 'paid'
GROUP BY p.category, p.product
ORDER BY p.category, rank_in_category
The OVER (...) clause is the window:
PARTITION BY p.category— rank each category separately; the numbering restarts at 1 for each one.ORDER BY … DESC— highest revenue gets rank 1.
The GROUP BY runs first (one row per product), then the window ranks those rows. That's why
the window can order by the aggregate.

2. A running total
Add a second SQL node, running_total, fed by the same two sources (a source can feed as
many nodes as you like):
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total
FROM (
SELECT to_char(o.order_date, '%Y-%m') AS month,
SUM(o.quantity * p.list_price) AS revenue
FROM input1 AS o
JOIN input2 AS p ON p.sku = o.sku
WHERE o.status = 'paid'
GROUP BY 1
) m
ORDER BY month
SUM(...) OVER (ORDER BY month) with no PARTITION BY is one window over everything, and
"everything up to this row, in month order" is the default frame — hence a running total.
order_date arrived as a real date (CSV inference got this one right), so to_char formats
it as 2025-01. A text function like substr fails on it — see the pitfalls.
Connect a Console to each SQL node and Execute.

Expected result
rank_in_category: 8 rows. Inseats: Pro seat (1, 114,787), Enterprise seat (2, 101,094), Starter seat (3, 46,197). Inservices, the Onboarding workshop is rank 1 with 103,200 despite selling far fewer units — it costs 1,200.running_total: 12 rows, from2025-01 · 42499.5 · 42499.5to2025-12 · 36974.1 · 467003— the same 467,003 as level 3.3.
Common pitfalls
The first argument of the substr function can only be a string, but got Date32.
The column is already a date. Use date functions — to_char(d, '%Y-%m'),
date_trunc('month', d) — instead of string ones.
Ranks don't restart per category.
PARTITION BY is missing, so the whole table is one window.
Two products share a rank and the next number is skipped.
That's RANK(): ties share a rank and leave a gap (1, 1, 3). Use DENSE_RANK() for no gaps,
or ROW_NUMBER() when you need exactly one row per position.
The running total restarts or jumps around.
The window's ORDER BY isn't the order you're reading in. The window's order and the final
ORDER BY are independent — set both.
What you built
Two tools you'll use constantly: ranking inside groups ("top 3 per category") and cumulative
sums. LAG(revenue) OVER (ORDER BY month) — the previous month's value — is the third, and
you already know enough to try it.
Check yourself
One question, drawn from a larger bank. Get it right to pass.
What does PARTITION BY p.category do in the RANK() window?