Advanced SQL – Window Functions

Learn how aggregate, rank, offset, and distribution window functions work‚ how to implement them efficiently, and how to solve the toughest SQL challenges elegantly.

Introduction

• Course introduction
• Course agenda

1. Tools, Files, and Query Processing Review

• Tools and demo database
• Using the demo and exercise files
• Logical query processing review

2. Window Functions and the OVER Clause

• How window functions fit in query processing
• Overview and filter clause
• PARTITION BY and ORDER BY

3. Framing, Exclusions, and Shortcuts

• Framing rows and ranges
• Practical framing examples
• Defaults, shortcuts, exclusions, and null handling

4. Aggregate Window Functions

• Aggregate grouped functions
• Aggregate window functions
• Combining grouped and window aggregate functions
• Challenge: Aggregate window functions
• Solution: Aggregate window functions

5. 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

6. 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