SQL - Introduction to Window Function - Rank Function Script by HadiMaster

-- Create the table
CREATE TABLE sales_data (
    ProductID SERIAL PRIMARY KEY,
    ProductName VARCHAR(50),
    Price DECIMAL(10, 2),
    ProductGroup VARCHAR(20)
);

-- Insert data into the table
INSERT INTO sales_data (ProductName, Price, ProductGroup) VALUES
('Product A', 10.99, 'Electronics'),
('Product B', 15.49, 'Clothing'),
('Product C', 20.99, 'Electronics'),
('Product D', 12.99, 'Home'),
('Product E', 8.99, 'Clothing'),
('Product F', 30.99, 'Electronics'),
('Product G', 25.49, 'Home'),
('Product H', 18.99, 'Clothing'),
('Product I', 22.99, 'Electronics'),
('Product J', 17.99, 'Home'),
('Product K', 9.99, 'Clothing'),
('Product L', 14.99, 'Electronics'),
('Product M', 11.49, 'Home'),
('Product N', 27.99, 'Electronics'),
('Product O', 21.99, 'Clothing'),
('Product P', 16.99, 'Home'),
('Product Q', 19.99, 'Electronics'),
('Product R', 13.99, 'Clothing'),
('Product S', 28.49, 'Home'),
('Product T', 23.99, 'Electronics'),
('Product U', 10.49, 'Clothing'),
('Product V', 26.99, 'Home'),
('Product W', 18.49, 'Electronics'),
('Product X', 14.49, 'Clothing'),
('Product Y', 29.99, 'Home'),
('Product Z', 24.49, 'Electronics'),
('Product AA', 11.99, 'Clothing'),
('Product AB', 15.99, 'Home'),
('Product AC', 20.49, 'Electronics'),
('Product AD', 17.49, 'Clothing'),
('Product BB', 17.49, 'Clothing');

-- see all data
SELECT * FROM sales_data;

-- ROW_NUMBER() Syntax

-- put row number based on price that sorted descendingly
SELECT
    ProductID,
    ProductName,
    Price,
    ProductGroup,
    ROW_NUMBER() OVER (ORDER BY Price DESC) AS row_num
FROM
    sales_data;

-- put row number based on table that
-- partitioned by product group and price that sorted ascendingly
SELECT
    ProductID,
    ProductName,
    Price,
    ProductGroup,
    ROW_NUMBER() OVER (PARTITION BY ProductGroup ORDER BY Price) AS group_row_num
FROM
    sales_data;

-- RANK

-- put rank based on table that
-- partitioned by product group and price that sorted ascendingly
SELECT
    ProductID,
    ProductName,
    Price,
    ProductGroup,
    RANK() OVER (PARTITION BY ProductGroup ORDER BY Price DESC) AS GroupRank
FROM
    sales_data;

-- put rank based on price that sorted descendingly
SELECT
    ProductID,
    ProductName,
    Price,
    ProductGroup,
    RANK() OVER (ORDER BY Price DESC) AS OverallRank
FROM
    sales_data;

-- DENSE_RANK() Syntax

-- put dense rank based on price and product name that sorted descendingly
-- and partitioned by product group
SELECT
    ProductID,
    ProductName,
    Price,
    ProductGroup,
    DENSE_RANK() OVER (PARTITION BY ProductGroup ORDER BY Price, ProductName) AS PriceNameRank
FROM
    sales_data;


-- NTILE() Syntax

-- devide data in 4 groups (quartile) based on price that sorted ascendingly
SELECT
    ProductID,
    ProductName,
    Price,
    ProductGroup,
    NTILE(4) OVER (ORDER BY Price) AS Quartile
FROM
    sales_data;

-- devide data in 4 groups (quartile) based on price that sorted ascendingly
-- and partitioned by product group
SELECT
    ProductID,
    ProductName,
    Price,
    ProductGroup,
    NTILE(4) OVER (PARTITION BY ProductGroup ORDER BY Price) AS GroupRank
FROM
    sales_data;