Skip to content
  • Official
  • Intermediate
  • English
  • 11 steps · 2 quizzes

SQL Window Functions: Interactive Lesson for Data Analysts

SQL window functions exercises: running totals, RANK and ties, moving averages with ROWS BETWEEN, LAG, and top N per group, with the window drawn as it slides.

  • Free
  • No account needed
  • Stop any time, pick up where you left off
Workshopy.io

WorkshopyOfficial

78 lessons · used by 2 teachers

For teachers

Teaching this? Run it live with your class.

  • Students join with a code — no accounts
  • See who’s done and who’s stuck, step by step
  • It’s your copy — change anything before class

Sign in to copy it to your workspace. It’s free.

Your path

11 steps · 2 quizzes · open any step, or read it here

  1. SQL window functions: a value from a window of rowsStart hereIn 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.InteractiveHands-on
    Read this step

    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.

    Interactive · Watch the window slide over the rows — try it in the lesson

    In this lesson you'll

    • put a total or an average beside every row with OVER (), and split it by group with PARTITION BY
    • number and rank rows with ROW_NUMBER, RANK and DENSE_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 LAG and LEAD, 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.

  2. OVER (): a total on every row, without losing a rowYou know aggregates: SELECT SUM(amount) FROM sales returns one row, 6850. The sixteen sales are gone; only their total is left. Add OVER () after the aggregate and it becomes a window functionInteractiveHands-on
    Read this step

    You know aggregates: SELECT SUM(amount) FROM sales returns one row, 6850. The sixteen sales are gone; only their total is left.

    Add OVER () after the aggregate and it becomes a window function:

    SELECT sale_day, rep, amount,
           SUM(amount) OVER () AS total
    FROM sales;
    

    Now all sixteen rows come back, and each one carries the total, 6850. OVER says "compute this over a window of rows"; the empty brackets say the window is the whole result. The rows are not folded together, so you can put each sale beside the total in one query, which is what a share of total needs:

    amount * 100.0 / SUM(amount) OVER () AS pct
    

    Every aggregate works this way: SUM, AVG, COUNT, MIN, MAX.

    One trap from arithmetic, not from windows: amount * 100 / SUM(amount) OVER () divides a whole number by a whole number, and PostgreSQL, SQL Server and SQLite drop the decimals: 5.84 becomes 5. Write 100.0.

    Try it: press Play, then change the function and what the column shows. Pick "amount * 100 / it" to see the whole-number trap. Then do the tasks.

    Interactive · OVER (): one window holding every row — try it in the lesson

    Takeaway: an aggregate with OVER () is computed over all the rows and written on every row; nothing is grouped away.

  3. PARTITION BY: one window per group, every row keptPARTITION BY splits the window into groups. SUM(amount) OVER (PARTITION BY region) computes North's total over North's rows and South's over South's, and writes each region's total on every row of that regionInteractiveHands-on
    Read this step

    PARTITION BY splits the window into groups. SUM(amount) OVER (PARTITION BY region) computes North's total over North's rows and South's over South's, and writes each region's total on every row of that region:

    SELECT sale_day, rep, region, amount,
           SUM(amount) OVER (PARTITION BY region) AS region_total
    FROM sales;
    

    It looks like GROUP BY, and the difference is the whole point:

    rows out what each row holds
    GROUP BY region 2, one per region the region and its totals; the single sales are gone
    OVER (PARTITION BY region) 16, all of them each sale, plus its region's total

    So a partition answers questions that need the row and its group side by side: this sale against the rep's best, this day against the region's average.

    Try it: switch Way between Window and GROUP BY and watch the rows fold or stay. Change the function and the partition, press Play, and see the window cover one colour at a time.

    Interactive · PARTITION BY keeps the rows, GROUP BY folds them — try it in the lesson

    Takeaway: PARTITION BY makes one window per group and keeps every row; GROUP BY makes one row per group.

  4. ROW_NUMBER, RANK and DENSE_RANK: numbering rows, and tiesSome window functions only make sense with an order. ORDER BY inside OVER sorts the rows of each window, and the ranking functions number them in that order: ROWNUMBER(): 1, 2, 3, 4 … every row a different number, ties or not.InteractiveHands-on
    Read this step

    Some window functions only make sense with an order. ORDER BY inside OVER sorts the rows of each window, and the ranking functions number them in that order:

    • ROW_NUMBER(): 1, 2, 3, 4 … every row a different number, ties or not.
    • RANK(): tied rows share a number, and the next number skips: 1, 2, 2, 4.
    • DENSE_RANK(): tied rows share a number, and the next one doesn't skip: 1, 2, 2, 3.

    The sales have ties: Chloe and Dario both sold 500, and three sales are 300. Ranked by amount, highest first, the two 500s are both 5th with RANK, and the next sale, 450, is 7th, because six sales are bigger. With DENSE_RANK that 450 is 6th: it counts the different amounts above it.

    Which of two tied rows ROW_NUMBER calls 5 and which 6 is up to the database, and it can change from run to run. When it matters, add a second column to break the tie: ORDER BY amount DESC, sale_day.

    ORDER BY inside OVER orders the window, not the result. The rows come back in any order unless the query ends with its own ORDER BY.

    Try it: press Play with ROW_NUMBER and watch the tied amounts, then switch to RANK and DENSE_RANK and play again. Then do the tasks.

    Interactive · ROW_NUMBER, RANK and DENSE_RANK on tied amounts — try it in the lesson

    Takeaway: ROW_NUMBER never ties; RANK shares a place and skips; DENSE_RANK shares a place and doesn't skip.

  5. Check: windows, partitions and ranksFour questions on what you've seen so far.Quiz · 4 questions
    Read the questions

    Four questions on what you've seen so far.

    Question 1. `sales` has 16 rows in 2 regions. How many rows does `SELECT region, SUM(amount) OVER (PARTITION BY region) FROM sales` return?

    • 2, one per region
    • 16, each with its region's total
    • 1, the grand total
    • 32

    Question 2. Amounts 700, 650, 500, 500, 450, ranked highest first. What is the 450's RANK?

    • 4
    • 5
    • 3
    • 6

    Question 3. And its DENSE_RANK?

    • 4
    • 5
    • 3
    • 6

    Question 4. What does `AVG(amount) OVER ()` put on each row?

    • The average of that row and the one before
    • Nothing: AVG needs GROUP BY
    • The average of all the rows, the same on every row
    • The row's own amount
  6. Running totals: SUM with ORDER BY inside OVERPut ORDER BY inside the OVER of an aggregate and it stops being one number for the window: it becomes a running value.InteractiveHands-on
    Read this step

    Put ORDER BY inside the OVER of an aggregate and it stops being one number for the window: it becomes a running value.

    SELECT day, opened,
           SUM(opened) OVER (ORDER BY day) AS running
    FROM tickets;
    

    For each day the window starts at the first day and ends at this day. On 2 March it holds one row, 42; on 3 March two rows, 42 + 38 = 80; on 11 March all ten, 433. That set of rows is the frame, and here it grows by one row each day.

    Add PARTITION BY and each group runs its own total, starting again at its first row: SUM(amount) OVER (PARTITION BY rep ORDER BY sale_day) is each rep's total so far.

    Any aggregate runs this way: MAX(opened) OVER (ORDER BY day) is the busiest day so far, COUNT(*) OVER (ORDER BY day) the number of days so far.

    Try it: press Play and watch the frame grow. Switch to "Each rep's sales" and see it start again at each colour. Then do the tasks: each one is a function and a window to choose.

    Interactive · A running total grows one row at a time — try it in the lesson

    Takeaway: ORDER BY inside OVER turns an aggregate into a running one: from the window's first row to the current row.

  7. Moving averages: ROWS BETWEEN 2 PRECEDING AND CURRENT ROWA running total keeps every day. A moving average keeps only the last few, so it follows the trend without being thrown by one odd day. You choose the frame yourselfInteractiveHands-on
    Read this step

    A running total keeps every day. A moving average keeps only the last few, so it follows the trend without being thrown by one odd day. You choose the frame yourself:

    SELECT day, opened,
           AVG(opened) OVER (ORDER BY day
                             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3
    FROM tickets;
    

    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means: the two rows before this one, and this one. Three rows, and as the current row moves down, the frame slides with it. On 4 March it averages 42, 38 and 51: 43.67. On 5 March the 42 drops out and the 47 comes in: 45.33.

    The pieces of a frame:

    bound means
    UNBOUNDED PRECEDING the first row of the window
    2 PRECEDING two rows before this one
    CURRENT ROW this row
    1 FOLLOWING one row after this one
    UNBOUNDED FOLLOWING the last row of the window

    Two things to know. The first rows have fewer rows before them, so 2 March averages one row and 3 March two; a report often leaves those out. And ROWS counts rows, not days: if a day were missing from the table, the frame would reach one day further back.

    Try it: press Play, then change the rows before and after and watch the frame change size. Look at the weekend, 7 and 8 March. Then do the tasks.

    Interactive · A moving average: the frame slides down the days — try it in the lesson

    Takeaway: ROWS BETWEEN n PRECEDING AND CURRENT ROW is a frame of a fixed number of rows that slides down with the current row.

  8. The default frame: why tied rows share a running totalIn the running total two steps ago you wrote no frame. When OVER has an ORDER BY and no frame, the database uses this oneInteractiveHands-on
    Read this step

    In the running total two steps ago you wrote no frame. When OVER has an ORDER BY and no frame, the database uses this one:

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    

    RANGE doesn't count rows: it takes every row whose ORDER BY value is the same as the current row's, its peers. With one row per day in tickets, that changes nothing. In sales there are four rows each day:

    SELECT sale_day, rep, amount,
           SUM(amount) OVER (ORDER BY sale_day) AS running
    FROM sales;
    

    The first row, Ana's 400 on 2 March, doesn't show 400. It shows 1550: the frame ends at the last row of 2 March, so all four of that day's sales are in it. All four rows of the day show 1550, then all four of 3 March show 3200. That is a running total by day, which is sometimes what you want, but it is not a total that grows on every row.

    Two fixes, when you want one:

    • Say ROWS: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW stops at this row, peers or not.
    • Make the order unique: ORDER BY sale_day, rep. With no ties there are no peers.

    Use both, so the order of the tied rows is fixed too. The same default trips LAST_VALUE: its frame ends at the current row (and its peers), so LAST_VALUE(amount) OVER (PARTITION BY rep ORDER BY sale_day) returns each row's own amount. To read the last row of the partition, give it the whole partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

    Try it: press Play with the default frame and watch the window take the whole day. Switch the frame to ROWS and play again. Then try LAST_VALUE, partitioned by rep, with each frame, and do the tasks.

    Interactive · The default frame takes every tied row — try it in the lesson

    Takeaway: with ORDER BY and no frame, the frame is RANGE up to the current row and its ties; write ROWS and a unique order for a row-by-row running total.

  9. LAG and LEAD: the row before and the row afterHow many more tickets than yesterday? That needs two rows on one line: today's and yesterday's. LAG reads a value from an earlier row of the window, LEAD from a later oneInteractiveHands-on
    Read this step

    How many more tickets than yesterday? That needs two rows on one line: today's and yesterday's. LAG reads a value from an earlier row of the window, LEAD from a later one:

    SELECT day, opened,
           LAG(opened) OVER (ORDER BY day) AS prev_day,
           opened - LAG(opened) OVER (ORDER BY day) AS day_change
    FROM tickets;
    
    • LAG(opened) is the previous row's opened, in the order of the window's ORDER BY. LAG(opened, 7) is seven rows back: a week ago, in a table with one row per day.
    • The first row has no row before it, so LAG returns NULL, and opened - NULL is NULL too. A third argument gives a value instead: LAG(opened, 1, 0).
    • LEAD is the same, looking forward: the last row's LEAD is NULL.
    • LAG and LEAD ignore the frame; they count rows of the partition. With PARTITION BY rep, Ben's first day reads nothing, not Ana's last day.

    Like ROWS, LAG counts rows, not days: it reads the previous row, whatever date it has.

    Try it: press Play, switch to LEAD, change how many rows away, and show the change instead of the value. Switch to "Every rep's sales" and see what LAG reads without a partition. Then do the tasks.

    Interactive · LAG and LEAD: reading another row — try it in the lesson

    Takeaway: LAG and LEAD put another row's value on this row; the first (or last) row gets NULL.

  10. Top N per group: filter a window in a CTEEach rep's best day. A ROWNUMBER per rep, ordered by amount, gives the best sale number 1, so the obvious query isInteractiveHands-on
    Read this step

    Each rep's best day. A ROW_NUMBER per rep, ordered by amount, gives the best sale number 1, so the obvious query is:

    SELECT rep, sale_day, amount,
           ROW_NUMBER() OVER (PARTITION BY rep ORDER BY amount DESC) AS rn
    FROM sales
    WHERE rn = 1;
    

    It fails: column "rn" does not exist. Putting the window itself in WHERE fails too: window functions are not allowed in WHERE. The reason is the order a query runs in:

    1. FROM, then WHERE: rows are filtered
    2. GROUP BY, then HAVING: groups are made and filtered
    3. window functions
    4. SELECT names the columns, then ORDER BY, then LIMIT

    WHERE runs before any window exists. So compute the window first, in a CTE, and filter the CTE:

    WITH ranked AS (
      SELECT rep, sale_day, amount,
             ROW_NUMBER() OVER (PARTITION BY rep ORDER BY amount DESC) AS rn
      FROM sales
    )
    SELECT rep, sale_day, amount
    FROM ranked
    WHERE rn = 1;
    

    Pick the function by what a tie should do. "Exactly one row per group": ROW_NUMBER. "The top two, ties kept": RANK() … WHERE rnk <= 2 can return more than two rows, and that is honest when two sales share second place.

    (Snowflake, BigQuery and DuckDB also have QUALIFY, a WHERE for windows: QUALIFY rn = 1. PostgreSQL, MySQL and SQL Server don't; the CTE works everywhere.)

    Try it: put the filter in the same query, then the window itself in WHERE, and read what the database says. Then move it into a CTE, press Play, and do the tasks.

    Interactive · Filter a window: compute it in a CTE first — try it in the lesson

    Takeaway: windows run after WHERE, GROUP BY and HAVING; to filter on one, compute it in a CTE and filter the CTE.

Comments

You’ll sign in with Google before it’s posted.Comments are public — don’t name your students.
0 / 1,500

No comments yet. Be the first to say how it went.

Using it

Workshopy shares this lesson under CC BY 4.0. Anyone may use and change it, with credit to you. Anyone can learn it here, free. A copy in your workspace is yours to change and run live; your students never need an account.

Versions

  1. 1

    Version 1Current

    · First published

Copies are made from the current version. A copy you already have doesn’t change.

More from Workshopy

All 78 lessons
All 21 Data & AI lessons