12. Ranking Functions.sql

use ecom;

select * from dim_product;

-- Lets see some very popular Window functions called ranking functions

-- RANKING
-- Suppose we have unit_price(90,90,100)
-- 1) row_number() - It will assign rank (1,2,3). Dosen't care about values. Ranking accordding to row number. Never assign same rank to 2 diferent records.
-- 2) rank() - It will assign rank (1,1,3). i.e. it will give same rank to same records, but it will start counting after calculating all the records.
-- 3) dense_rank() - It will assign rank (1,1,2).

select
	unit_price,
    row_number() over (order by unit_price) as 'row_number', -- sort the unit_price and give rank accordingly
    rank() over (order by unit_price) as 'rank',
    dense_rank() over (order by unit_price) as 'dense_rank'
from
	dim_product;
    
-- PARTITION
-- What if we want to reset the ranking based on some partition? Suppose data have different categories, we may have to reset the counting for each category.

select
	unit_price,
    category,
    row_number() over (partition by category order by unit_price) as 'row_number', -- sort the unit_price according to category and reset rank for each category
    rank() over (partition by category order by unit_price) as 'rank',
    dense_rank() over (partition by category order by unit_price) as 'dense_rank'
from
	dim_product;