Step 1 of 11
SQL window functions: a value from a window of rows
In this lesson you practise SQL window functions on two small tables from work: a running total, a rank inside each group, a moving average and the change from the day before. Every exercise runs here, the window is drawn over the rows as it slides, and you check your answers as you go.
A window function computes a value for each row from a set of rows around it, its window, and keeps every row. Pick a window below and press Play: the amber bar is the window, the blue column fills one row at a time.
In this lesson you'll
- put a total or an average beside every row with
OVER (), and split it by group withPARTITION BY - number and rank rows with
ROW_NUMBER,RANKandDENSE_RANK, and know what each does with ties - write running totals and moving averages, choose the frame with
ROWS BETWEEN, and avoid the default-frame trap - compare a row with the one before it using
LAGandLEAD, and keep the top rows per group with a CTE
About 85 minutes, with a short check after the first three ideas, a playground, a practice set and a final quiz.
The two tables:
| table | columns | rows |
|---|---|---|
sales | sale_day, rep, region, amount | 16: four reps (Ana and Ben in North, Chloe and Dario in South), 2 to 5 March 2026 |
tickets | day, opened | 10: support tickets opened each day, 2 to 11 March 2026 |
The numbers are illustrative.