Advanced SQL Series: Window Functions
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.
- At least two years of SQL programming experience
- A basic understanding of query processing
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.
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)