SQL Window Functions Practice Exercises

What is a window function?

A window function performs a calculation across a set of related rows — its "window" — without collapsing them into a single row the way GROUP BY does. Every window function uses the OVER() clause to define that window.

-- Rank every order by price, highest first, without losing any rows
SELECT order_id, customer_id, price_usd,
       ROW_NUMBER() OVER (ORDER BY price_usd DESC) AS price_rank
FROM online_bookstore_orders;

Unlike a plain aggregate query, this still returns one row per order — it just adds a calculated column alongside the original data.

PARTITION BY — resetting the calculation per group

PARTITION BY splits the rows into groups before the window function runs, so the calculation restarts for each group. It behaves like GROUP BY, except the rows themselves stay intact.

-- Rank orders by price within each genre separately
SELECT order_id, book_genre, price_usd,
       RANK() OVER (PARTITION BY book_genre ORDER BY price_usd DESC) AS genre_rank
FROM online_bookstore_orders;

Here, the ranking restarts at 1 for every genre instead of ranking across the entire table.

Running totals with SUM() OVER

Aggregate functions like SUM, AVG, and COUNT can also be used as window functions. Combined with ORDER BY inside OVER(), they produce a running total instead of a single collapsed value.

-- Running total of each member's workout time, ordered by visit date
SELECT member_id, visit_date, workout_duration_minutes,
       SUM(workout_duration_minutes) OVER (
           PARTITION BY member_id ORDER BY visit_date
       ) AS running_total
FROM gym_member_visits;

The ORDER BY inside OVER() tells the database to add up rows progressively, not all at once — each row's total includes itself and every prior row in its partition.

Common mistakes

Using a window function in WHERE — window functions run after WHERE is evaluated, so you can't filter on one directly in the same query. Wrap the query in a subquery or CTE and filter in the outer query instead.

Forgetting ORDER BY inside OVER() — for ranking or running totals, leaving out ORDER BY means the row order (and therefore the result) is undefined. For a plain partitioned total with no order, it's fine to omit it.

Confusing window functions with GROUP BY — GROUP BY collapses rows into one per group; a window function keeps every row and just attaches a calculated value to it.

Mixing up PARTITION BY and ORDER BY — PARTITION BY defines which rows belong together; ORDER BY defines the sequence used for ranking or running calculations within that group. They do different jobs and are often used together.