The route

Window functions

Download the exercise files

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.csv and products.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.

Products ranked inside their category

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.

Monthly revenue and its running total

Expected result

  • rank_in_category: 8 rows. In seats: Pro seat (1, 114,787), Enterprise seat (2, 101,094), Starter seat (3, 46,197). In services, the Onboarding workshop is rank 1 with 103,200 despite selling far fewer units — it costs 1,200.
  • running_total: 12 rows, from 2025-01 · 42499.5 · 42499.5 to 2025-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.

  1. What does PARTITION BY p.category do in the RANK() window?