SQL Window Functions Explained

About window functions

Window functions are probably the most confusing section of SQL. You might think, "So what? They just came up with some additional functions". Not really. "Window functions" is a separate language built into regular SQL. And it's more complicated than everything you know about SELECTs.

In short, window functions assist in making great analytical reports without Excel. Maybe you want to calculate monthly sales percentages over the year? Window functions. Split marketing channels into effective and ineffective ones? Window functions. Choose the top 10 clients for each segment? Same.

About the book

This book is a clear and visual introduction to window functions. Clear — because I can describe complex topics in a readable way. Visual — because I have prepared a hundred pictures and GIFs to help you understand SQL windows.

Window functions are a complex topic. So the book only teaches a little at a time. It gives just enough theory and a lot of practice, because it’s the only way to turn abstract knowledge into skills.

The book starts with SQL windows basics (Part 1) and goes through frame nuances (Part 2) to some pretty advanced stuff (Part 3).

Book contents

Here is what you will learn:

1. Why use window functions
2. Windows and functions
   • Ranking
   • Offset
   • Aggregation
   • Rolling aggregates
   • Statistics
3. Frames
   • ROWS and GROUPS
   • RANGE
   • EXCLUDE
   • FILTER
4. Practice
   • Finance
   • Clustering
   • Data cleaning

About the author

I'm Anton Zhiyanov, a Python/Golang developer and SQLite enthusiast. I work on open source, teach courses and blog about programming.

In 2021 I launched a course on SQL window functions. It now has 1000+ graduates and an average rating of 5 stars based on 300+ student reviews.

In 2023 I decided to write a book based on the original course. Here it is.

Book details

• 296 pages
• 100+ pictures and GIFs
• 56 interactive exercises (with solutions)
• PDF 17.8 × 23.3 cm (7 × 9.2 inches)