Advanced SQL – Window Functions

Advanced SQL – Window Functions
Advanced SQL – Window Functions
English | MP4 | AVC 1920×1080 | AAC 48KHz 2ch | 1h 56m | 380 MB

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
Table of Contents

Introduction
1 Course introduction
2 Course agenda

Tools Files and Query Processing Review
3 Tools and demo database
4 Using the demo and exercise files
5 Logical query processing review

Window Functions and the OVER Clause
6 How window functions fit in query processing
7 Overview and filter clause
8 PARTITION BY and ORDER BY

Framing Exclusions and Shortcuts
9 Framing rows and ranges
10 Practical framing examples
11 Defaults shortcuts exclusions and null handling

Aggregate Window Functions
12 Aggregate grouped functions
13 Aggregate window functions
14 Combining grouped and window aggregate functions
15 Challenge Aggregate window functions
16 Solution Aggregate window functions

Rank and Distribution Window Functions
17 The concept of rank
18 ROW NUMBER and NTILE
19 RANK and DENSE RANK
20 Distribution window functions
21 Challenge Rank window functions
22 Solution Rank window functions

Offset Window Functions
23 Offset window functions
24 Row offset window functions
25 Frame offset window functions
26 Challenge Offset window functions
27 Solution Offset window functions

Conclusion
28 Review conclusion and next steps