Window Functions and Advanced Analytics

Purpose and Use Cases

Window vs Aggregate Functions

OVER Clause Fundamentals

PARTITION BY Clause

ORDER BY in Windows

Frame Boundaries

ROW_NUMBER Function

RANK Function

DENSE_RANK Function

NTILE Function

Ranking with Ties

Running Totals

Moving Averages

Cumulative Distributions

Window-based MIN and MAX

LAG Function

LEAD Function

FIRST_VALUE Function

LAST_VALUE Function

NTH_VALUE Function

PERCENT_RANK Function

CUME_DIST Function

PERCENTILE_CONT Function

PERCENTILE_DISC Function

Multiple Window Specifications

Named Windows

Filtering Window Results

Window Functions in Subqueries