Modern SQL: Aggregation and Analysis

This course helps developers making better use of their SQL database.

Why this workshop?

SQL is a powerful tool that can easily replace hundreds lines of code by a few lines of SQL—in the end, the SQL query will even run faster as the home-grown code.

This workshop gives a grounds-up introduction into window functions (aka. the OVER clause), one of the SQL features to leverage that benefit.


What will you learn?

This workshop covers the full power of the OVER clause:

  • Partitioning
  • Ordering
  • Framing (rows, range, groups)

Naturally, it also covers all functions that can be used with the OVER clause:

  • Aggregate functions
  • Ranking functions
  • Distribution functions
  • Functions to access other rows

Use cases we will discuss and explore in the exercises include:

  • Implementing “Top-N per group” queries
  • Finding consecutive events in an event stream
  • Using the “carry forward” technique to accelerate large aggregate queries
  • Optimizing performance when using multiple window functions.

Who is the trainer?


 Markus Winand is an independent author, trainer and consultant on all things SQL. His book “SQL Performance Explained” has just been translated into a fifth language. It can be purchased as a print edition, and it’s also available to read for free on his use-the-index-luke.com website. Markus is currently working on his second book centered on the idea that things have changed a lot since SQL-92. It is likewise available free of charge online (modern-sql.com).

Who is it for?

This workshop is for developers who feel comfortable with basic SQL (select, from, join, where, group by).

WorkTechnical requirements:

Bring your own device with a database pre-installed. PostgreSQL 11 is the recommended database for this training, as it has the most complete implementation of window functions. Alternatively, the training can be taken on any of the following databases. Please make sure you have at least the mentioned version.

- MySQL 8.0
- MariaDB 10.2
- PostgreSQL 11
- SQLite 3.25
- SQL Server 2012
- Oracle 12
- Db2 (LUW) 11.1

Prior to the workshop, the trainer will provide a download of a VirtualBox appliance with free open-source databases pre-installed (PostgreSQL, MariaDB, MySQL). You still need to install the required client software on your device as the appliance does not include the client software.



Independent author, trainer, consultant & author of "SQL Performance Explained"