Window analysis

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.

Exercise Window analysis

Compute each row's rank and change from the previous price with ROW_NUMBER and LAG.

Show example solution

SELECT date, price,
  ROW_NUMBER() OVER (ORDER BY date) AS rn,
  price - LAG(price) OVER (ORDER BY date) AS change
FROM stocks;

Test yourself

Window analysis: match the terms

ROW_NUMBER

rank
LAG

previous
OVER

window
trend

trend
What gets the previous value?
- LEAD
- LAG
- RANK
- COUNT
Show answer

LAG — LAG gets the previous value.

Next lesson: Procedure and trigger

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies