SQL practice

SQL window functions practice questions

33 problems, ordered from easiest. They run in your browser, and 2 are free.

Start with 3-day moving average of transactions

The idea in a minute

A window function calculates across a set of related rows but keeps every row. That is the difference from GROUP BY, which squashes rows into one per group.

You write the function, then OVER with a PARTITION BY (the groups) and an ORDER BY (the sequence). Ranking, running totals and comparing a row with the previous one are all this one idea.

Example: Rank orders inside each city

CREATE TABLE orders AS SELECT * FROM (VALUES
 (1,'Asha','Pune',450),(2,'Ravi','Pune',300),(3,'Meera','Delhi',900),(4,'Asha','Delhi',150),(5,'Ravi','Pune',250)
) t(order_id, customer, city, amount);
SELECT city, order_id, amount,
       RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS rank_in_city
FROM orders
ORDER BY city, rank_in_city

What it prints

city   order_id  amount  rank_in_city
Delhi  3         900     1
Delhi  4         150     2
Pune   1         450     1
Pune   2         300     2
Pune   5         250     3

What to watch for

RANK leaves gaps after a tie, DENSE_RANK does not, and ROW_NUMBER never repeats.
A window function cannot be used directly in WHERE. Put it in a subquery or CTE first, then filter.
Without ORDER BY inside OVER, a running total is the same on every row.

Window Functions problems

Want a guided order across every topic? Open the practice sheets or the SQL hub.