Typorithm · medium

Window Functions

window-functionsclickhousewindowoverrunning-total

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn Window Functions

Querying

Window functions use OVER (PARTITION BY ... ORDER BY ...) to compute running totals and row numbers per partition without collapsing rows like GROUP BY does.

OVER turns an aggregate like sum() into a window function that keeps every input row.

PARTITION BY splits rows into independent windows, and ORDER BY sets the order within each window.

With ORDER BY present, sum() becomes a running total and row_number() numbers rows in sequence.

OVER (...) computes per row, no collapse.

PARTITION BY resets the window per key.

ORDER BY makes sum() a running total.

SELECT
    user_id,
    value,
    sum(value) OVER (
        PARTITION BY user_id
        ORDER BY ts
    ) AS running
FROM events;

Read the implementation

Connect each part of the code to the concept before you type.

1. Window aggregate

OVER turns sum() into a window function that does not collapse rows.

sum(value) OVER (

2. Per-user window

PARTITION BY restarts the calculation for each user.

PARTITION BY user_id

3. Window order

ORDER BY ts defines the running order for the cumulative sum.

ORDER BY ts

4. Running total

Each row keeps its own running total alongside the original value.

AS running

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.