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 you'll 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.
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
Common questions
- What do I need to know before I start?
- SELECT, WHERE, ORDER BY, functions like SUM and COUNT, GROUP BY and queries with WITH (CTEs). If any of those is new to you, the SQL for Data People course teaches them from scratch; this course starts right where that one ends.
- 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.
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 one question per page, on up to five pages a day.
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've worked with data for about ten years, mostly at mission-driven organizations: policy research first, then education, and now early childhood services. 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