SQL - Window Function - Value Function Script by HadiMaster

CREATE TABLE sales_data (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2),
    product_group VARCHAR(20)
);

INSERT INTO sales_data (product_name, price, product_group) VALUES
('Product A', 100.00, 'Group 1'),
('Product B', 150.00, 'Group 2'),
('Product C', 80.00, 'Group 1'),
('Product D', 120.00, 'Group 3'),
('Product E', 200.00, 'Group 2'),
('Product F', 90.00, 'Group 1'),
('Product G', 110.00, 'Group 3'),
('Product H', 130.00, 'Group 4'),
('Product I', 95.00, 'Group 1'),
('Product J', 180.00, 'Group 2'),
('Product K', 85.00, 'Group 1'),
('Product L', 115.00, 'Group 3'),
('Product M', 210.00, 'Group 2'),
('Product N', 70.00, 'Group 1'),
('Product O', 125.00, 'Group 4'),
('Product P', 100.00, 'Group 1'),
('Product Q', 160.00, 'Group 2'),
('Product R', 75.00, 'Group 1'),
('Product S', 105.00, 'Group 3'),
('Product T', 220.00, 'Group 2'),
('Product U', 65.00, 'Group 1'),
('Product V', 135.00, 'Group 4'),
('Product W', 110.00, 'Group 1'),
('Product X', 170.00, 'Group 2'),
('Product Y', 80.00, 'Group 1'),
('Product Z', 95.00, 'Group 3'),
('Product AA', 230.00, 'Group 2'),
('Product BB', 60.00, 'Group 1'),
('Product CC', 140.00, 'Group 4');

-- melihat keseluruhan data
SELECT * FROM sales_data;

-- find cheapest product from all product based on its price
SELECT
    product_id,
    product_name,
    price,
    product_group,
    FIRST_VALUE(product_name) OVER (ORDER BY price) AS cheapest_product
FROM
    sales_data;

-- find cheapest product based on each group sorted by its price
SELECT
    product_id,
    product_name,
    price,
    product_group,
    FIRST_VALUE(product_name) OVER (PARTITION BY product_group ORDER BY price) AS cheapest_product
FROM
    sales_data;

-- find priciest product from all product based on its price
SELECT
    product_id,
    product_name,
    price,
    product_group,
    LAST_VALUE(product_name) OVER (ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS priciest_product
FROM
    sales_data;

-- find product based on its sorted price then pick the last product name
SELECT
    product_id,
    product_name,
    price,
    product_group,
    LAST_VALUE(product_name) OVER (ORDER BY price ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS last_product
FROM
    sales_data;

-- find priciest product for every group sorted by its price
SELECT
    product_id,
    product_name,
    price,
    product_group,
    LAST_VALUE(product_name) OVER (PARTITION BY product_group ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS priciest_product
FROM
    sales_data;

-- find 1 lag of product name for every product group sorted by its price
SELECT
    product_id,
    product_name,
    price,
    product_group,
    LAG(product_name) OVER (PARTITION BY product_group ORDER BY price) AS previous_price_in_group
FROM
    sales_data;

-- find 2 lag of product name for every product group sorted by its price
SELECT
    product_id,
    product_name,
    price,
    product_group,
    LAG(product_name, 2) OVER (PARTITION BY product_group ORDER BY price) AS previous_price_in_group
FROM
    sales_data;

-- find 1 lead of product name for every product group sorted by its price
SELECT
    product_id,
    product_name,
    price,
    product_group,
    LEAD(product_name) OVER (PARTITION BY product_group ORDER BY price) AS previous_price_in_group
FROM
    sales_data;

-- find 2 lead of product name for every product group sorted by its price
SELECT
    product_id,
    product_name,
    price,
    product_group,
    LEAD(product_name,2) OVER (PARTITION BY product_group ORDER BY price) AS previous_price_in_group
FROM
    sales_data;