Lesson 8 of 14 · 28 min
Window frames: choosing exactly which rows count
By the end of this lesson you can write explicit frame clauses, choose between ROWS, RANGE and GROUPS, use time-based RANGE offsets on dates, exclude the current row, and use FIRST_VALUE and LAST_VALUE correctly.
A window has three layers:
- Partition (PARTITION BY): which rows are related at all.
- Order (ORDER BY): the sequence inside each partition.
- Frame: for the current row, which part of the partition the function actually looks at.
Aggregates (SUM, AVG, MIN, MAX, COUNT) and the value functions FIRST_VALUE, LAST_VALUE and NTH_VALUE use the frame. Ranking functions and LAG/LEAD ignore it: they always see the whole partition.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons