Learn how aggregate, rank, offset, and distribution window functions work‚ how to implement them efficiently, and how to solve the toughest SQL challenges elegantly.
Overview
Syllabus
Introduction
- Course introduction
- Course agenda
- Tools and demo database
- Using the demo and exercise files
- Logical query processing review
- How window functions fit in query processing
- Overview and filter clause
- PARTITION BY and ORDER BY
- Framing rows and ranges
- Practical framing examples
- Defaults, shortcuts, exclusions, and null handling
- Aggregate grouped functions
- Aggregate window functions
- Combining grouped and window aggregate functions
- Challenge: Aggregate window functions
- Solution: Aggregate 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
- Row offset window functions
- Frame offset window functions
- Challenge: Offset window functions
- Solution: Offset window functions
- Review, conclusion, and next steps
Taught by
Ami Levin
