Quick answer: SQL window functions run a calculation across a set of rows related to the current row, without collapsing them like GROUP BY does. The most used are ROW_NUMBER(), RANK(), DENSE_RANK(), SUM()/AVG() OVER(), LAG(), LEAD() and NTILE(). Below are 15 practice problems with ready-to-run answers and short explanations, built on one simple sales table.
The sample table
Every problem below uses this one table, sales. Create it in PostgreSQL, MySQL 8+, or any modern SQL engine and follow along.
CREATE TABLE sales (
id INT,
rep VARCHAR(20),
region VARCHAR(20),
sale_date DATE,
amount INT
);
INSERT INTO sales VALUES
(1,'Ali','North','2026-01-05',500),
(2,'Ali','North','2026-01-20',300),
(3,'Sara','North','2026-01-11',800),
(4,'Sara','North','2026-02-02',200),
(5,'Omar','South','2026-01-09',400),
(6,'Omar','South','2026-02-15',900),
(7,'Hina','South','2026-01-22',700),
(8,'Hina','South','2026-02-28',100);
Ranking problems (ROW_NUMBER, RANK, DENSE_RANK)
1. Number each sale per region, newest first
SELECT rep, region, sale_date, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date DESC) AS rn
FROM sales;
ROW_NUMBER() gives every row a unique number inside its partition (region). It never ties.
2. Find the latest sale in each region
SELECT * FROM (
SELECT s.*,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date DESC) AS rn
FROM sales s
) t
WHERE rn = 1;
Wrap the window function in a subquery, then filter rn = 1. You cannot use a window function directly in WHERE.
3. Rank reps by total sales (ties share a rank)
SELECT rep, SUM(amount) AS total,
RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk
FROM sales
GROUP BY rep;
RANK() leaves gaps after a tie (1,1,3). DENSE_RANK() would give 1,1,2. You can use window functions on top of GROUP BY.
4. RANK vs DENSE_RANK side by side
SELECT amount,
RANK() OVER (ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense
FROM sales;
5. Top 2 sales per region
SELECT * FROM (
SELECT s.*,
DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dr
FROM sales s
) t
WHERE dr <= 2;
Aggregate window problems (SUM, AVG OVER)
6. Show each sale next to its region total
SELECT rep, region, amount,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;
Unlike GROUP BY, this keeps every row and adds the total beside it.
7. Each sale as a percent of its region total
SELECT rep, region, amount,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct
FROM sales;
8. Running total of sales over time (whole company)
SELECT sale_date, amount,
SUM(amount) OVER (ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;
Adding ORDER BY inside OVER turns the window into a running frame. The ROWS clause makes the running total explicit.
9. Running total per region
SELECT region, sale_date, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS region_running
FROM sales;
10. 2-row moving average
SELECT sale_date, amount,
AVG(amount) OVER (ORDER BY sale_date
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;
Offset problems (LAG, LEAD) and NTILE
11. Compare each sale to the rep's previous sale
SELECT rep, sale_date, amount,
LAG(amount) OVER (PARTITION BY rep ORDER BY sale_date) AS prev_amount
FROM sales;
LAG() pulls a value from an earlier row. The first row per rep returns NULL (no previous sale).
12. Change vs previous sale (and a default for the first row)
SELECT rep, sale_date, amount,
amount - LAG(amount, 1, 0) OVER (PARTITION BY rep ORDER BY sale_date) AS change
FROM sales;
The third argument of LAG (0 here) replaces NULL for the first row.
13. Show each rep's next sale date
SELECT rep, sale_date,
LEAD(sale_date) OVER (PARTITION BY rep ORDER BY sale_date) AS next_sale
FROM sales;
14. Split all sales into 4 equal buckets (quartiles)
SELECT rep, amount,
NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales;
15. First and last sale amount per rep in one row set
SELECT rep, sale_date, amount,
FIRST_VALUE(amount) OVER (PARTITION BY rep ORDER BY sale_date) AS first_sale,
LAST_VALUE(amount) OVER (PARTITION BY rep ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS last_sale
FROM sales;
LAST_VALUE needs the full frame (UNBOUNDED FOLLOWING), or it only "sees" up to the current row and returns the wrong value. This is the single most common window-function bug.
3 mistakes to avoid
- Filtering on a window alias in WHERE. It does not work; wrap it in a subquery or CTE and filter outside.
- Forgetting the frame with LAST_VALUE. Always add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
- Confusing RANK and DENSE_RANK. RANK leaves gaps after ties; DENSE_RANK does not.
Keep practising
Once these click, put them to work: try them in a real build from our data engineering projects, see where SQL fits in the data science roadmap, and note that these same functions power most models in our dbt tutorial for beginners.
FAQ
What is the difference between GROUP BY and a window function?
GROUP BY collapses rows into one row per group. A window function keeps every row and adds the calculated value beside it, so you can show a detail row and its group total together.
Can I use window functions in MySQL?
Yes, from MySQL 8.0 onward. They also work in PostgreSQL, SQL Server, Oracle, SQLite 3.25+, and BigQuery.
Why does my window function return NULL?
LAG and LEAD return NULL at the edges of a partition because there is no previous or next row. Pass a third default argument, for example LAG(amount, 1, 0), to replace it.
The mistake that still catches me most often is LAST_VALUE(): by default it only looks as far as the current row, so it silently returns the wrong value until you set the frame to UNBOUNDED FOLLOWING. I now build every window query on a tiny table like the 8-row one above before running it on a real dataset.






