O'Reilly logo
live online training icon Live Online training

Advanced SQL Series: Window Functions

Ami Levin

Structured Query Language—more commonly known as SQL—is an amazing declarative programming language. While easy enough to get started, a basic command of the syntax and familiarity with simple use cases will only get you so far. The trickery that you can apply to writing highly efficient and refined logic is mind-blowingly complex. As a result, producing elegant, efficient, and scalable set-based solutions in SQL can be very challenging.

Join expert Ami Levin for a thrilling, action-packed dive into window functions in SQL. Ami will take apart common programming challenges that are typically hard to solve using conventional means and teach you how to solve them using pure set-based SQL. Along the way, Ami demonstrates methodologies and techniques that you can later use to solve any other SQL challenge. These newly acquired tools will not only make your everyday programming tasks easier and more fun but also impress your colleagues and managers.

What you'll learn-and how you can apply it

By the end of this live, online course, you’ll understand:

  • Window functions basic concepts
  • How window functions are processed
  • How to use window functions to solve SQL challenges

And you’ll be able to:

  • Write queries using window functions
  • Approach SQL challenges with a proven, easy methodology
  • Improve performance, readability, and scalability of your SQL queries

This training course is for you because...

  • You're an experienced SQL developer who wants to take your skills to a new level.
  • You're often stumped with challenges that seem too hard to solve in SQL.

Prerequisites

  • At least two years of SQL programming experience
  • A basic understanding of query processing

Recommended preparation:

SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, Fourth edition (book)

About your instructor

  • Ami Levin is a senior data tier architect, data modeler, database designer, and SQL developer with over 20 years of experience. Over his career, he has invented intelligent, autonomous performance optimization products and designed, reviewed, and optimized some of the world’s largest, most challenging mission-critical data environments. Ami is a seasoned speaker, instructor, mentor, and author of a number of whitepapers and technical articles.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Introduction and review of query processing (15 minutes)

  • Lecture: How SQL engines evaluate and process your queries logically; the order of execution for the various clauses; how intermediate data is moved between them; why this is so important

The OVER clause syntax (25 minutes)

  • Lecture: ORDER BY; PARTITION BY; FILTER; ROWS and RANGE

How window functions "break" the processing model (10 minutes)

  • Lecture: Logical processing order; where are window functions allowed

Break (10 minutes)

Aggregate window functions (35 minutes)

  • Lecture: How GROUP BY queries lose the original detailed values; SUM, COUNT, MIN, MAX, and AVG
  • Hands-on exercise: Solve running aggregate challenges with window functions

Ranking window functions (30 minutes)

  • Lecture: RANK, DENSE_RANK, ROW_NUMBER, NTILE
  • Hands-on exercise: Use window functions to solve ranking challenges

Break (10 minutes)

Offset window functions (20 minutes)

  • Lecture: LEAD, LAG, and NTH_VALUE
  • Hands-on exercise: Use offset window functions techniques

Analytical window functions (15 minutes)

  • Lecture: CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK
  • Hands-on exercise: Use analytical window functions

Wrap-up and Q&A (10 minutes)