Summary - Window functions

Write a month-over-month report with ROW_NUMBER and LAG.

Summary - Window functions

Window functions compute without collapsing rows. ROW_NUMBER, RANK, LAG and PARTITION BY are common.

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk
FROM products;

Summary window query.

name, price, RANK() OVER (ORDER BY price DESC) AS rk is selected from products. RANK() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.

Exercise Summary - Window functions

Summary: find each product's price rank with RANK() OVER (ORDER BY price DESC).

Show example solution

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk
FROM products;

Test yourself

What defines the window in a window function?
- WHERE
- OVER
- FROM
- GROUP BY
Show answer

OVER — OVER defines the window.
Summary - Window functions: match the terms

ROW_NUMBER

row number
RANK

rank
LAG

previous
PARTITION BY

group

Next lesson: Analytics design

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies