Module lessons (3/4)
Rankings: ROW_NUMBER, RANK and DENSE_RANK
Rankings: ROW_NUMBER, RANK and DENSE_RANK
Beyond aggregating, window functions shine at ordering and ranking. There are functions designed specifically to work inside windows (OVER() becomes mandatory) that assign sequential numbers or ranking positions.
To make them work against a criterion, we have to use the ORDER BY clause inside OVER().
The ranking trio
ROW_NUMBER(): Assigns a plain sequential number (1, 2, 3...) to every row, breaking ties arbitrarily.RANK(): Produces the "Olympics" effect: 1, 2, 2, 4. It leaves a gap (the "3" is skipped) and assigns the same rank in case of ties.DENSE_RANK(): Densifies ranks: 1, 2, 2, 3. There is never a gap after a tie.
SELECT
product_name,
price,
RANK() OVER(ORDER BY price DESC) AS price_rank
FROM products;By combining PARTITION BY and ORDER BY together inside OVER() we can get "the top 3 for each category"!
Extract 'first_name', 'last_name' and 'id' from the customers table ('customers') (assume 'id' is the identifier number). Add a 'rank' column using DENSE_RANK() based on 'id' descending (from highest to lowest).
Show hint
DENSE_RANK() OVER(ORDER BY id DESC).
Solution available after 3 attempts
Build a ranking for each 'category_id' based on the price of the products ('products'). Extract 'category_id', 'id', 'price' and add 'price_rank' using ROW_NUMBER() in descending 'price' order, partitioning by 'category_id'.
Show hint
Combine PARTITION BY category_id ORDER BY price DESC inside the OVER.
Solution available after 3 attempts