This course teaches the foundations and intricacies of window function processing and how to use it to implement practical solutions to everyday challenges. You can learn how to use different constructs and advanced solution techniques and how to utilize the declarative and composable nature of SQL and its processing order. By the end of the course you’ll better understand the fundamental pros and cons of each method.
This course was created by Ami Levin. We are pleased to offer this training in our library.
- The OVER and FILTER clauses
- Framing, exclusions, and shortcuts
- Aggregate window functions
- Rank window functions
- Distribution window functions
- Offset window functions
Skill Level Advanced
(bright music) - [Ami] You're an experienced SQL developer. You manage to get stuff done in time. Your manager and colleagues are generally happy. For the most part, you're happy with your SQL skills too. And yet, you frequently encounter a SQL challenge that frustrates you to no end. And eventually, you do come up with a solution. You just know there must be a better way. All these subqueries and complex logic just don't seem right. Figuring out your own code, the one you wrote only one hour ago, seems like a futile exercise in a foreign language. And you already know that when it gets to QA, they will scream to high Heaven because of the query's horrendous performance. Window functions literally open new windows of processing logic and solution opportunities that once seen can't be unseen. This course will save you hours of hair pulling, allow you to write queries that are clear, elegant and efficient. Your QA team is going to send you flowers. Like any fundamentally different feature, it may seem hard at first. But don't worry. We will tackle window functions from the ground up. Layer by layer, you will realize just how powerful of a tool they are. We will start with a quick review of query processing order and how window functions fit in the grand scheme of it. You will learn all about the OVER clause, partitioning, ordering, filtering, framing, all using common-use cases for the various types of window functions. This course is not about the syntax. Yeah, sure, we will cover the syntax. But our focus will be on the foundational understanding of how window functions are processed, how to use them effectively and how to avoid the most common pitfalls. For this course, you will need a minimum of one year of SQL experience. But more importantly, an analytical, inquisitive mindset. It's not going to be an easy ride. I do promise that it's going to be fun-packed, challenging and extremely rewarding.