Window Functions in SQL
By the end, you'll be able to write queries with OVER, PARTITION BY, ROW_NUMBER, RANK, LAG and window frames, and explain why a running total repeats numbers or why a LAG skips a month.
You go at your own pace, with your Maestro beside you, 1:1, through the whole course: ask it anything, and it asks you questions too.
12 skills · 5 modules
What will you be able to do?
Each box is a skill. The base is at the bottom, recognizing and explaining, and you work your way up to building something of your own and judging what works and why. Hover over a box to see what it builds on. Click it to see where I teach it and how your Maestro checks it.
- Synthesize
- Analyze
- Apply
- Understand
Synthesize
Design a query that ranks within each group and keeps the top rows, using PARTITION BY, a ranking function and QUALIFY
Where I teach it: Where windows run in a query · How do I get the top 3 in each group in SQL, ties included?
Builds on:
- Choose between ROW_NUMBER, RANK and DENSE_RANK based on how they treat ties
- Filter on a window's result with QUALIFY or WITH, because WHERE runs first
- Place windows and QUALIFY in a query's logical order to predict what each clause sees
Your Maestro asks you to build something of your own with it and saves it as evidence when you do.
Analyze
Choose between ROW_NUMBER, RANK and DENSE_RANK based on how they treat ties
Where I teach it: Number rows and keep one · What's the difference between ROW_NUMBER, RANK and DENSE_RANK?
Builds on:
- Number the rows in each group in a chosen order with ROW_NUMBER()
Your Maestro asks you to break a problem down with it and saves it as evidence when you do.
Detect when LAG skips a missing period by measuring the distance between dates
Where I teach it: Compare with the row before · Why does LAG skip a missing month, and how do I find it?
Builds on:
- Compare each row with the previous or next row in its group using LAG and LEAD
Your Maestro asks you to break a problem down with it and saves it as evidence when you do.
Explain why a running total repeats numbers on ties and choose between ROWS and RANGE
Where I teach it: Running totals and moving averages · Why does my running total in SQL repeat numbers?
Builds on:
- Write a running total by group with SUM() OVER (PARTITION BY ... ORDER BY ...)
Your Maestro asks you to break a problem down with it and saves it as evidence when you do.
Place windows and QUALIFY in a query's logical order to predict what each clause sees
Where I teach it: Where windows run in a query · In what order does a SQL query with window functions run?
Builds on:
- Filter on a window's result with QUALIFY or WITH, because WHERE runs first
Your Maestro asks you to break a problem down with it and saves it as evidence when you do.
Apply
Put each group's total or percentage next to every row with OVER (PARTITION BY ...)
Where I teach it: Keep every row · How do I show each group's total next to every row in SQL?
Builds on:
- Explain how a window function computes a value for each row without collapsing rows, unlike GROUP BY
Your Maestro asks you to use it on a new case and saves it as evidence when you do.
Number the rows in each group in a chosen order with ROW_NUMBER()
Where I teach it: Number rows and keep one · How do I number the rows in each group with ROW_NUMBER?
Builds on:
- Put each group's total or percentage next to every row with OVER (PARTITION BY ...)
Your Maestro asks you to use it on a new case and saves it as evidence when you do.
Filter on a window's result with QUALIFY or WITH, because WHERE runs first
Where I teach it: Number rows and keep one · How do I get the latest row for each group in SQL?
Builds on:
- Number the rows in each group in a chosen order with ROW_NUMBER()
Your Maestro asks you to use it on a new case and saves it as evidence when you do.
Compare each row with the previous or next row in its group using LAG and LEAD
Where I teach it: Compare with the row before · How do I compare each row with the previous month in SQL with LAG?
Builds on:
- Put each group's total or percentage next to every row with OVER (PARTITION BY ...)
Your Maestro asks you to use it on a new case and saves it as evidence when you do.
Write a running total by group with SUM() OVER (PARTITION BY ... ORDER BY ...)
Where I teach it: Running totals and moving averages · How do I write a running total in SQL?
Builds on:
- Put each group's total or percentage next to every row with OVER (PARTITION BY ...)
Your Maestro asks you to use it on a new case and saves it as evidence when you do.
Calculate a moving average with ROWS BETWEEN n PRECEDING AND CURRENT ROW
Where I teach it: Running totals and moving averages · How do I calculate a 3-month moving average in SQL?
Builds on:
- Write a running total by group with SUM() OVER (PARTITION BY ... ORDER BY ...)
Your Maestro asks you to use it on a new case and saves it as evidence when you do.
Understand
Explain how a window function computes a value for each row without collapsing rows, unlike GROUP BY
Where I teach it: Keep every row · How do I show each group's total next to every row in SQL?
Your Maestro asks you to explain it in your own words and saves it as evidence when you do.
Who is this course for?
For anyone who already writes SQL with SELECT, WHERE, GROUP BY and WITH, and wants to calculate without losing rows: put the total next to each row, number rows, compare with the previous month and calculate running totals. It starts where SQL for Data People ends. You practice in DuckDB inside Maestro, even if you use a different database at work.
Ask your Maestro
Your Maestro is an AI that knows this course. Ask it about the first page and it answers right here, free.
You: Where do I start?
Your Maestro: From what you already know. Tell me what you've done with data, and I'll start this page from there.
Asking about: Answers from the page "How do I show each group's total next to every row in SQL?"
Only this browser keeps what you ask here. How it works
What we'll cover
Keep every row
Number rows and keep one
Compare with the row before
Running totals and moving averages
Where windows run in a query
How long does it take?
1 to 1.5 hours
A guide: you go at your own pace, and the course waits where you left off.
Common questions
- What is a window function in SQL?
- A window function calculates something over a group of related rows without collapsing them into one, as GROUP BY does. With OVER and PARTITION BY you can put the total next to each row, number rows, look at the previous row with LAG, or accumulate. You learn each one with queries that run on the page.
- When do I use ROW_NUMBER and when RANK?
- Use ROW_NUMBER when you want exactly one row per group, for example each customer's latest order, even when there are ties. Use RANK when ties should share a position. The course shows what each one does when two rows tie and how to keep one row per group.
- What do I need to know before starting?
- SELECT, WHERE, ORDER BY, functions like SUM and COUNT, GROUP BY and queries with WITH (CTE). If any of these is new, the SQL for Data People course teaches them from scratch, and this one starts right where that one ends. Queries run in DuckDB inside Maestro, so you install nothing.
- Do I need a course, or can I learn this on my own?
- You can learn it on your own, and many people do. A course gives you the order: what to practice first, ready-to-use data, and exercises that show where you go wrong before it happens at work. This course is free, and your Maestro answers questions as you go. If you prefer going solo, the method still works.
- Is it free? Do I need an account?
- It's free. You can read every page without an account. With a free account you enroll in one tap, run the queries on each page and ask your Maestro questions.
- Do I have to install anything?
- No. The queries run in DuckDB inside Maestro: you type on the page and see the result right below.
- I use a different database at work. Will this help?
- You practice in DuckDB. Whenever we use QUALIFY, which not every database supports, you also see the same query written with a CTE and WHERE, which works without QUALIFY. If you're not sure how your database handles something, ask your Maestro.
- Is the data real?
- No. The branch sales, orders and products are made up for the course, built so the tricky cases show up: ties, a missing month and two orders on the same day.
How do I check what AI does? I teach it in Your AI and Your Data: Don't Let It Make Up the Number and Check what AI gives you in three steps.
What you get
- 5 modules and 12 pages, open whenever you want them.
Open to everyone: read all of it, and your Maestro, an AI, answers your questions on every page.
With a free account: your Maestro for the whole course, always on the page you're on, and the exercises run with nothing to install.
Words you'll meet
In the lessons, tap one to see what it means, or ask your Maestro about it.
Who's teaching you
Sergio Sánchez
I'm Sergio, chekos online. I'm a data architect, and I've spent more than ten years working with data in the Bay Area, mostly at mission-driven organizations: policy research first, then education, and now early childhood services. I grew up in Tijuana. In 2018 I started tacosdedatos to share what I was learning, and since then it's been a blog, a podcast, courses, guides, and a community. These days I spend a lot of my time building with AI agents and writing about how I organize my work around them. I share what I've learned; you take what's useful and make it yours.
See you in the course
Window Functions in SQL