Advanced SQL – Window Functions
1h 57mAdvanced2020-05-28
Authors
Ami Levin
Data Educator, Speaker, and Practitioner, SQL and RDM enthusiast
Course details
Window functions are one of the most radical, fundamental enhancements to modern SQL. They allow access to neighboring rows without using subqueries, thus enabling amazing opportunities for concise, elegant, high-performing solutions.
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.
Topics include:
The OVER and FILTER clauses
Framing, exclusions, and shortcuts
Aggregate window functions
Rank window functions
Distribution window functions
Offset window functions
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.
Topics include:
The OVER and FILTER clauses
Framing, exclusions, and shortcuts
Aggregate window functions
Rank window functions
Distribution window functions
Offset window functions
Skills covered
SQLDatabase AdministrationAdvancedDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware Development
Concepts
Introduction
- Course introduction
- Course agenda
Tools, Files, and Query Processing Review
- Tools and demo database
- Using the demo and exercise files
- Logical query processing review
Window Functions and the OVER Clause
- How window functions fit in query processing
- Overview and filter clause
- PARTITION BY and ORDER BY
Framing, Exclusions, and Shortcuts
- Framing rows and ranges
- Practical framing examples
- Defaults, shortcuts, exclusions, and null handling
Aggregate Window Functions
- Aggregate grouped functions
- Aggregate window functions
- Combining grouped and window aggregate functions
- Challenge - Aggregate window functions
- Solution - Aggregate window functions
Rank and Distribution Window Functions
- The concept of rank
- ROW_NUMBER and NTILE
- RANK and DENSE_RANK
- Distribution window functions
- Challenge - Rank window functions
- Solution - Rank window functions
Offset Window Functions
- Offset window functions
- Row offset window functions
- Frame offset window functions
- Challenge - Offset window functions
- Solution - Offset window functions
Conclusion
- Review, conclusion, and next steps