Window analysis
- SQL course
- Advanced
- Lesson 79/81
- 6 min
Do ranking and delta analysis with window functions.
Window analysis
In this challenge you will do trend analysis with ROW_NUMBER and LAG.
SELECT date, price,
ROW_NUMBER() OVER (ORDER BY date) AS rn,
price - LAG(price) OVER (ORDER BY date) AS change
FROM stocks;Rank and change.
date, price, ROW_NUMBER() OVER (ORDER BY date) AS rn, … is selected from stocks. ROW_NUMBER() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.
Practice Window analysis
Compute each row's rank and change from the previous price with ROW_NUMBER and LAG.
Starter code
-- window analysis
SELECT date, price,
ROW_NUMBER() OVER (ORDER BY date) AS rnSample solution
SELECT date, price,
ROW_NUMBER() OVER (ORDER BY date) AS rn,
price - LAG(price) OVER (ORDER BY date) AS change
FROM stocks;Expected output
date rn change 2026-01-01 1 NULL 2026-01-02 2 5
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Window analysis: match the terms
| Term | Meaning |
|---|---|
ROW_NUMBER |
rank |
LAG |
previous |
OVER |
window |
trend |
trend |
2 What gets the previous value?
- LEAD
- LAG Correct answer
- RANK
- COUNT
Explanation: LAG gets the previous value.