Explore the power of windowing functions in SQL in this comprehensive conference talk from PASS Data Community Summit. Learn how to efficiently retrieve and analyze data across related rows without complex self-joins. Discover general windowing functions essential for every database developer, as well as lesser-known techniques. Understand the use of aggregate functions like COUNT(*) in windowing scenarios. Dive into topics such as ROW_NUMBER, framing, LAG and LEAD functions, FIRST_VALUE and LAST_VALUE, distribution functions, and partitioning. Gain practical insights on handling gaps in data and applying these concepts to real-world scenarios. By the end of this talk, master the art of writing more efficient and elegant SQL queries using windowing functions.
Overview
Syllabus
Introduction
What are Windowing Functions
Row Number
Aggregate Functions
Windowing Functions
Framing
New Windowing Functions
Lag Lag Lead
Lag by Days
Lag by Offset
No Previous Orders
First Last Value
Last Order Date
Distribution Functions
Cumulative vs Percent Rank
Percent Continuous vs Distribution
Partitioning by Postal Code
Finding the Gaps
Recommended Books
Ben Gann
Taught by
PASS Data Community Summit
