5 SQL Window-Function Exercises Taken From Real Business Problems

Prashant Kumar Dey — Thu, 22 Oct 2026 09:31:07 +0000

TL;DR: SQL window functions (OVER (PARTITION BY ... ORDER BY ...)) calculate across related rows without collapsing them like GROUP BY does. Practise five patterns analysts use daily: running totals with SUM() OVER, gaps between events with LAG(), first-per-group with ROW_NUMBER(), month-over-month change with LAG() on an aggregate, and top-N with ties using RANK(). You can run every exercise in a free browser SQL lab.

Window functions are the point where most SQL learners stall. The syntax isn't hard; knowing when to reach for them is. These five exercises come from tasks analysts actually get. Try each one before reading the hint.

Use any Postgres or MySQL 8 database, or open a free browser lab at bartman.ai/labs/sql so there's nothing to install.

Setup

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  order_date  DATE,
  amount      NUMERIC(10,2)
);

INSERT INTO orders VALUES
 (1, 101, '2026-01-04', 1200), (2, 102, '2026-01-09',  450),
 (3, 101, '2026-02-02',  800), (4, 103, '2026-02-11', 2300),
 (5, 102, '2026-03-15',  300), (6, 101, '2026-03-28',  950),
 (7, 104, '2026-03-30',  610), (8, 103, '2026-04-02', 1800);

1. Running revenue per customer

Task: For each order, show the customer's cumulative spend up to and including that order.

Hint
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date)

2. Days since the previous order

Task: For each order, how many days since the same customer's previous order? First orders should show NULL.

Hint
order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)

3. Each customer's first order only

Task: Return one row per customer: their first order. No GROUP BY joins back to the table.

Hint
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) in a CTE, then filter = 1.

4. Month-over-month revenue change

Task: Total revenue per month, plus the percentage change from the previous month.

Hint
Aggregate by DATE_TRUNC('month', order_date) in a CTE, then LAG(total) OVER (ORDER BY month).

5. Top customer each month, ties included

Task: For each month, the customer(s) with the highest spend. If two tie, show both.

Hint
RANK(), not ROW_NUMBER(). That one word is the whole exercise.

Key takeaways

A window function keeps every row; GROUP BY collapses rows.
PARTITION BY sets the group, ORDER BY sets the sequence inside it.
LAG() and LEAD() compare a row with its neighbours.
Use RANK() when ties should share a position, ROW_NUMBER() when you need exactly one row.

FAQ

What is a window function in SQL?
A function that calculates a value across a set of rows related to the current row, defined by an OVER() clause, while still returning every individual row.

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?
ROW_NUMBER() gives unique numbers even for ties. RANK() gives ties the same number and skips the next ones (1, 1, 3). DENSE_RANK() gives ties the same number without gaps (1, 1, 2).

Do MySQL and PostgreSQL both support window functions?
Yes. PostgreSQL has supported them since 8.4 and MySQL since 8.0. SQLite supports them from 3.25.

Where can I practise SQL window functions online for free?
In any browser-based SQL sandbox. Bartman's SQL lab needs no install, and its projects check your query against the expected result.

Where to go next

Once these feel easy, the real test is applying them to a messy business question: churn by signup cohort, multi-touch attribution, or receivables ageing. Those are full projects rather than snippets. I put a few together on Bartman, for example subscription churn with CTEs and window functions and e-commerce funnel and cohort retention, where your queries are run and checked against the expected result.

Post your solution to #5 in the comments. I'm curious how many people reach for ROW_NUMBER() first.

Written with help from AI tools.

DEV Community: Prashant Kumar Dey