20.Window Function

My complete SQL Masterclass Notes in Tanglish is now live! Covers 30+ topics with syntax, examples, and step-by-step explanations. 
Perfect for both beginners and professionals to master SQL easily. Check it out here 👉 https://topmate.io/dataengineering/1697791

====================================================================================



Window Function 
----------------
CREATE TABLE Sales (
    TransactionID INT,
    Store VARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO Sales (TransactionID, Store, SalesAmount)
VALUES
    (1, 'A', 100.00),
    (2, 'A', 200.00),
    (3, 'A', 150.00),
    (4, 'B', 250.00),
    (5, 'B', 300.00);


select store , sum(SalesAmount) from sales 
group by store ;


SELECT TransactionID, Store, SalesAmount,
       SUM(SalesAmount) OVER (PARTITION BY Store order by TransactionID desc ) AS TotalSales
FROM Sales;


ROW_NUMBER
----------------

SELECT TransactionID, Store, SalesAmount,
       ROW_NUMBER() OVER (partition by store ORDER BY TransactionID asc) AS RowNum
FROM Sales;

select TransactionID, Store, SalesAmount,
ROW_NUMBER() OVER (partition by  STORE ORDER BY SalesAmount DESC ) AS ROWNUM 
from sales;


drop table if exists Employees;
CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(100),
    Department VARCHAR(50),
    HireDate DATE
);


INSERT INTO Employees (EmployeeID, Name, Department, HireDate)
VALUES
    (1, 'Alice', 'HR', '2020-05-01'),
    (1, 'Alice', 'HR', '2022-06-15'),
    (2, 'Bob', 'IT', '2021-07-10'),
    (3, 'Charlie', 'Finance', '2020-09-30'),
    (3, 'Charlie', 'Finance', '2022-05-22');

select EmployeeID,Name,Department,HireDate , 
ROW_NUMBER() over ( order by EmployeeID asc) as rownum 
from employees 

RANK and DENSE RANK
--------------------
drop table if exists students;
CREATE TABLE Students (
    StudentID INT,
    StudentName VARCHAR(100),
    ExamScore INT
);

INSERT INTO Students (StudentID, StudentName, ExamScore)
VALUES
    (1, 'Alice', 95),
    (2, 'Bob', 90),
    (3, 'Charlie', 95),
    (4, 'David', 85),
    (5, 'Eva', 90);

select StudentID,StudentName,ExamScore,
rank() over (order by ExamScore desc ) as score_rank from students

select StudentID,StudentName,ExamScore,
dense_rank() over (order by ExamScore desc ) as score_rank from students


NTILE
----------------


CREATE TABLE EmployeeSales (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    SalesAmount DECIMAL(10, 2)
);


INSERT INTO EmployeeSales (EmployeeID, EmployeeName, SalesAmount) VALUES
(1, 'Alice', 10000),
(2, 'Bob', 8500),
(3, 'Charlie', 7500),
(4, 'David', 6000),
(5, 'Eva', 11000),
(6, 'Frank', 4500),
(7, 'Grace', 3000),
(8, 'Hank', 4000),
(9, 'Ivy', 8000),
(10, 'Jack', 9500);



SELECT EmployeeID, EmployeeName, SalesAmount,
       NTILE(4) OVER (ORDER BY SalesAmount DESC) AS PerformanceGroup
FROM EmployeeSales;


PERCENT_RANK
----------------

CREATE TABLE ProductSales (
    ProductID INT,
    ProductName VARCHAR(100),
    SalesAmount INT
);


INSERT INTO ProductSales (ProductID, ProductName, SalesAmount) VALUES
(1, 'Product 1', 500),
(2, 'Product 2', 600),
(3, 'Product 3', 550),
(4, 'Product 4', 700),
(5, 'Product 5', 750),
(6, 'Product 6', 800),
(7, 'Product 7', 850),
(8, 'Product 8', 900),
(9, 'Product 9', 950),
(10, 'Product 10', 1000),
(11, 'Product 11', 600),
(12, 'Product 12', 650),
(13, 'Product 13', 700),
(14, 'Product 14', 750),
(15, 'Product 15', 800),
(16, 'Product 16', 850),
(17, 'Product 17', 900),
(18, 'Product 18', 950),
(19, 'Product 19', 1000),
(20, 'Product 20', 100),
(21, 'Product 21', 200),
(22, 'Product 22', 250),
(23, 'Product 23', 300),
(24, 'Product 24', 350),
(25, 'Product 25', 400),
(26, 'Product 26', 450),
(27, 'Product 27', 500),
(28, 'Product 28', 550),
(29, 'Product 29', 600),
(30, 'Product 30', 650),
(31, 'Product 31', 700),
(32, 'Product 32', 750),
(33, 'Product 33', 800),
(34, 'Product 34', 850),
(35, 'Product 35', 900),
(36, 'Product 36', 950),
(37, 'Product 37', 1000),
(38, 'Product 38', 550),
(39, 'Product 39', 600),
(40, 'Product 40', 650),
(41, 'Product 41', 700),
(42, 'Product 42', 750),
(43, 'Product 43', 800),
(44, 'Product 44', 850),
(45, 'Product 45', 900),
(46, 'Product 46', 950),
(47, 'Product 47', 1000),
(48, 'Product 48', 550),
(49, 'Product 49', 600),
(50, 'Product 50', 650);


SELECT ProductID, ProductName, SalesAmount,
       PERCENT_RANK() OVER (ORDER BY SalesAmount DESC) AS PercentRank,
       RANK() OVER (ORDER BY SalesAmount DESC) AS Rank_s

FROM ProductSales;




LAG
----------------
drop table if exists EmployeeSalaries;
CREATE TABLE EmployeeSalaries (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Salary DECIMAL(10, 2),
    Year INT
);
INSERT INTO EmployeeSalaries (EmployeeID, EmployeeName, Salary, Year) VALUES
(1, 'Alice', 5000, 2023),
(1, 'Alice', 5500, 2024),
(2, 'Bob', 4500, 2023),
(2, 'Bob', 4800, 2024),
(3, 'Charlie', 4000, 2023),
(3, 'Charlie', 4200, 2024),
(4, 'David', 4600, 2023),
(4, 'David', 4700, 2024),
(5, 'Eva', 5200, 2023),
(5, 'Eva', 5400, 2024);


select * ,
lag(Salary) over (partition by EmployeeID order by year) as PreviousYearSalary
from EmployeeSalaries;

select * ,
lag(Salary) over (partition by EmployeeID order by year) as PreviousYearSalary,
salary - lag(Salary) over (partition by EmployeeID order by year) as SalaryChange
from EmployeeSalaries;

LEAD
----------------
CREATE TABLE SalesData (
    SaleID INT,
    EmployeeName VARCHAR(100),
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE
);
INSERT INTO SalesData (SaleID, EmployeeName, SaleAmount, SaleDate) VALUES
(1, 'Alice', 5000, '2025-01-01'),
(2, 'Bob', 3000, '2025-01-02'),
(3, 'Charlie', 4000, '2025-01-03'),
(4, 'David', 4500, '2025-01-04'),
(5, 'Eva', 5500, '2025-01-05');

SELECT SaleID, EmployeeName, SaleDate, SaleAmount,
       LEAD(SaleAmount) OVER (ORDER BY SaleDate) AS NextSaleAmount
FROM SalesData;


FIRST_VALUE
----------------
drop table if exists EmployeeSalaries;
CREATE TABLE EmployeeSalaries (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Salary DECIMAL(10, 2),
    Year INT
);
INSERT INTO EmployeeSalaries (EmployeeID, EmployeeName, Salary, Year) VALUES
(1, 'Alice', 5000, 2021),
(1, 'Alice', 5500, 2022),
(1, 'Alice', 6000, 2023),
(1, 'Alice', 6500, 2024),
(1, 'Alice', 7000, 2025),
(2, 'Bob', 4500, 2023),
(2, 'Bob', 4800, 2024),
(3, 'Charlie', 4000, 2023),
(3, 'Charlie', 4200, 2024),
(4, 'David', 4600, 2023),
(4, 'David', 4700, 2024),
(5, 'Eva', 5200, 2023),
(5, 'Eva', 5400, 2024);

select *,
first_value (Salary) over (partition by EmployeeID order by Year 
                         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as firstsalary,
 first_value (Salary) over (partition by EmployeeID order by Year 
                         )- salary as diff
from EmployeeSalaries;

LAST_VALUE 
----------------
select *,
last_value (Salary) over (partition by EmployeeID order by Year 
                         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as lastsalary,
 last_value (Salary) over (partition by EmployeeID order by Year 
                         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)- salary as diff
from EmployeeSalaries;


NTH_VALUE 
----------------
SELECT EmployeeID, EmployeeName, Salary,
       NTH_VALUE(Salary, 2) OVER (PARTITION BY EmployeeID ORDER BY year) AS ThirdSalary
FROM EmployeeSalaries;