SQL Window functions Code for article - https://www.analyticsvidhya.com/blog/2020/12/window-function-a-must-know-sql-concept/

sql_window_functions_01.sql

/* Sample data */
insert into emp (EMPID, NAME, JOB, SALARY)
values
(201, 'ANIRUDDHA', 'ANALYST', 2100),
(212, 'LAKSHAY', 'DATA ENGINEER', 2700),
(209, 'SIDDHARTH', 'DATA ENGINEER', 3000),
(232, 'ABHIRAJ', 'DATA SCIENTIST', 2500),
(205, 'RAM', 'ANALYST', 2500),
(222, 'PRANAV', 'MANAGER', 4500),
(202, 'SUNIL', 'MANAGER', 4800),
(233, 'ABHISHEK', 'DATA SCIENTIST', 2800),
(244, 'PURVA', 'ANALYST', 2500),
(217, 'SHAROON', 'DATA SCIENTIST', 3000),
(216, 'PULKIT', 'DATA SCIENTIST', 3500),
(200, 'KUNAL', 'MANAGER', 5000);

sql_window_functions_02.sql

/*Print total salary*/
select *, sum(salary) from emp;

sql_window_functions_03.sql

/*Print total salary per job category*/
select job, sum(salary) from emp group by job;

sql_window_functions_04.sql

/*Print total salary wtih every row*/
select *,
sum(salary) OVER() as total_salary
from emp;

sql_window_functions_05.sql

/*Print total salary per job category*/
select *,
sum(salary) OVER(PARTITION BY job) as total_job_salary
from emp;

sql_window_functions_06.sql

/*Order rows within partitions*/
select *,
sum(salary) over(partition by job order by salary desc) as ordered_job_salary
from emp;

sql_window_functions_07.sql

/*Number rows using row_number*/
select *, ROW_NUMBER() over() as "row_number" from emp;

sql_window_functions_08.sql

/*Number rows within each partition*/
select *, ROW_NUMBER() over(partition by job order by salary) as "partition_row_number" from emp;

sql_window_functions_09.sql

/*Rank rows within each partition using rank function*/
select *, 
ROW_NUMBER() over(partition by job order by salary) as "row_number",
RANK() over(partition by job order by salary) as "rank_row" 
from emp;

sql_window_functions_10.sql

/*Rank rows within each partition using dense_rank function*/
select *, 
ROW_NUMBER() over(partition by job order by salary) as "row_number",
RANK() over(partition by job order by salary) as "rank_row",
DENSE_RANK() over(partition by job order by salary) as "dense_rank_row"
from emp;

sql_window_functions_11.sql

/*Print first value within each partition using nth_value function*/
select *, 
NTH_VALUE(name, 1) over(partition by job order by salary asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as "FIRST"
from emp;

sql_window_functions_12.sql

/*Print last value within each partition using nth_value function*/
select *, 
NTH_VALUE(name, 1) over(partition by job order by salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as "LAST"
from emp;

sql_window_functions_13.sql

/*Print nth value within each partition using nth_value function*/
select *, 
NTH_VALUE(name, 3) over(partition by job order by salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as "THIRD"
from emp;

sql_window_functions_14.sql

/*Statistics using ntile function*/
select *, 
NTILE(4) over(order by salary) as "quartile"
from emp;

sql_window_functions_15.sql

/*Lead values*/
select *,
LEAD(salary, 1) Over(partition by job order by salary) as sal_next
from emp;

sql_window_functions_16.sql

/*Lag values*/
select *,
LAG(salary, 1) Over(partition by job order by salary) as sal_previous,
salary - LAG(salary, 1) Over(partition by job order by salary) as sal_diff
from emp;

Thanks

great examples

thanks

Thank you for this. The concepts were well covered

Thank you!

Thank you!