SQL Window Functions Explained [book]
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 or Pandas. 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.
I want people to understand SQL windows better. So I wrote 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.
A step-by-step narrative combined with numerous visuals helps readers understand the material. Fifty-six interactive exercises with solutions ensure that you master window functions in practice.
You can use the book with any of the following DBMSs:
- MySQL 8.0.2+ (MariaDB 10.2+)
- PostgreSQL 11+
- SQLite 3.28+
- MS SQL 2012+
- Oracle 11g+
What you will learn
Part 1. Windows and functions
Part 2. Frames
- ROWS and GROUPS
- RANGE
- EXCLUDE
- FILTER
Part 3. Practice
- Finance
- Clustering
- Data cleaning
Detailed table of contents
Introduction
About the book
Why use window functions
Playground
Part 1. Windows and functions
Ranking
- Window function
- Window ordering vs. result ordering
- Sorting uniqueness
- Multiple windows
- Partitions
- Groups
- Ranking functions
Offset
- Comparing with neighbors
- Comparing to boundaries
- Window, partition, frame
- Comparing to boundaries revisited
- Offset functions
Aggregation
- Partitioned aggregates
- Filtering and execution order
- Window definition
- Aggregation functions
Rolling aggregates
- Moving average
- Frame
- Cumulative total
- Default frame
- Rolling aggregates functions
Statistics
- Cumulative distribution
- Relative rank
- Summary statistics
- Percentile
- Percentile as a window function
- Statistics functions
Summary
Part 2. Frames
ROWS and GROUPS
- Frame revisited
- Groups frame
RANGE
- Range frame
- Range specifics
- Frame boundaries
- Default frame
- Frames FAQ
EXCLUDE
- Excluding rows
- Exclude options
FILTER
- Filtering rows
- CASE as an alternative to FILTER
Summary
Part 3. Practice
Finance
- Aggregate first, windows later
- Windows first, filter later
- Aggregation and null
- Gold plan sales
- Sales by plan for Q1
- Platinum plan moving average
- Comparison with December
- Plans contribution
- High/medium/low revenue
- 2020 vs 2019
- Months ranked by revenue
Clustering
- Identifying islands
- Islands with duplicates
- Date islands
- Value clusters
- Date clusters
Data cleaning
- Duplicates
- Missing values
- Previous non-empty value
- Outliers
Final thoughts
The book starts with SQL windows basics (Part 1) and goes through frame nuances (Part 2) to some pretty advanced stuff (Part 3).
To preview the first few chapters, visit the links in the table of contents above.
About the author
I'm Anton Zhiyanov, 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)
Get the ebook for $20 or paperback for $35
Subscribe to keep up with new posts.