Stage 2 · Lesson 2 of 9

Window functions

calculate a value for each row by looking at a group of related rows. Unlike GROUP BY, they keep every row instead of squashing the group into one. ROW_NUMBER, RANK and DENSE_RANK number the rows inside each group, called a partition.

LAG and LEAD read the previous or next row. SUM and AVG with an ORDER BY give a running total or a moving average. In pipelines, their most common job is .

For each , such as an order ID, number its rows from newest updated_at to oldest, then keep only row 1. That gives you the latest record per key. They also help split activity into sessions, find gaps in a sequence, and pick each user's first or last event. Learn the PARTITION BY, ORDER BY and frame parts carefully.

Common trapusing ROW_NUMBER without an ORDER BY that always gives the same order, so ties pick a different row each run.

A question to check yourself, an AI practice session and 2 cheat sheet entries come with the full roadmap. Unlock

Resources for this stage

Start with

CourseFree
SQL Tutorial for Data Analysis (formerly Mode)
ThoughtSpot · Beginner
Its advanced section covers window functions, performance tuning and pivoting on realistic data

Practise

LeetCode SQL 50
Fifty graded problems; the window function and aggregation ones match what pipeline SQL needs
Select Star SQL
An interactive book on one real dataset that ends with window functions and self joins

Keep handy

Kimball Dimensional Modeling Techniques
The original definitions of grain, conformed dimensions and SCD types, straight from the source
roadmap.sh: SQL
Check which SQL topics you still lack before moving on

Also worth your time

Data Warehouse Design: Schemas, Steps, and Best Practices (opens in a new tab)Read
Star and snowflake schemas, facts and dimensions, and the steps of a warehouse design in one read