SQL Window Functions Explained

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.

I've read several dozen articles explaining SQL window functions. They all suffered from one of two problems:

• Easy read without much practical value, describing 10% of features.
• Difficult to comprehend. If I did not know the subject — I would not understand a thing.

SQL window queries can look like this for an unfamiliar reader.

I want people to understand SQL windows better. So I decided to write a book — SQL Window Functions Explained.

About the book

It 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.

Here is what you will learn:

1. Why use window functions
2. Windows and functions
   • Ranking
   • Offset
   • Aggregation
   • Rolling aggregates
3. Frames
   • ROWS and GROUPS
   • RANGE
   • EXCLUDE
   • FILTER
4. Practice
   • Product analytics
   • Handling duplicates
   • Finding the gaps

Every chapter covers a single topic in depth

10 of the 13 chapters are ready, and I’ll publish the rest by May 2023.

And here is an excerpt from the book.