Anton Zhiyanov
blog contacts ru

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

  1. Easy read without much practical value, describing 10% of features.
  2. Difficult to comprehend. If I did not know the subject — I would not understand a thing.
How I see complex queries
SQL window queries can look like this for an unfamiliar reader.

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.

Book cover
Page 3
Page 15
Page 30
Page 58
Page 62
Page 88
Page 126

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+

Get the book

What you will learn

Why use window functions

Part 1. Windows and functions

Part 2. Frames

  • ROWS and GROUPS
  • RANGE
  • EXCLUDE
  • FILTER

Part 3. Practice

  • Finance
  • Clustering
  • Data cleaning
Book poster
Every chapter covers a single topic in depth
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, 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)

Get the ebook for $20 or paperback for $35

Follow @ohmypy on Twitter to keep up with new posts 🚀

data