SQL Window Functions Explained: ROW_NUMBER, RANK & DENSE_RANK

In this video, we'll dive into SQL Window Functions, specifically focusing on ROW_NUMBER, RANK, and DENSE_RANK. You'll learn how to use these functions to:

Fetch Top N Records: Retrieve the top 2 employees from each department.
Rank Employees: Identify the top 3 employees by salary in each department.
Understand Ranking Differences: See how ROW_NUMBER, RANK, and DENSE_RANK work with sample data.
We'll also solve an interview problem on energy consumption using SQL window functions.

drop table employee;
create table employee
( emp_ID int
, emp_NAME varchar(50)
, DEPT_NAME varchar(50)
, SALARY int);

insert into employee values(101, 'Mohan', 'Admin', 4000);
insert into employee values(102, 'Rajkumar', 'HR', 3000);
insert into employee values(103, 'Akbar', 'IT', 4000);
insert into employee values(104, 'Dorvin', 'Finance', 6500);
insert into employee values(105, 'Rohit', 'HR', 3000);
insert into employee values(106, 'Rajesh', 'Finance', 5000);
insert into employee values(107, 'Preet', 'HR', 7000);
insert into employee values(108, 'Maryam', 'Admin', 4000);
insert into employee values(109, 'Sanjay', 'IT', 6500);
insert into employee values(110, 'Vasudha', 'IT', 7000);
insert into employee values(111, 'Melinda', 'IT', 8000);
insert into employee values(112, 'Komal', 'IT', 10000);
insert into employee values(113, 'Gautham', 'Admin', 2000);
insert into employee values(114, 'Manisha', 'HR', 3000);
insert into employee values(115, 'Chandni', 'IT', 4500);
insert into employee values(116, 'Satya', 'Finance', 6500);
insert into employee values(117, 'Adarsh', 'HR', 3500);
insert into employee values(118, 'Tejaswi', 'Finance', 5500);
insert into employee values(119, 'Cory', 'HR', 8000);
insert into employee values(120, 'Monica', 'Admin', 5000);
insert into employee values(121, 'Rosalin', 'IT', 6000);
insert into employee values(122, 'Ibrahim', 'IT', 8000);
insert into employee values(123, 'Vikram', 'IT', 8000);
insert into employee values(124, 'Dheeraj', 'IT', 11000);

select * from employee
order by dept_name, emp_id desc

-- 1) Fetch the first 2 employees from each department to join the company.
select *
from (
select *
, row_number() over(partition by dept_name order by emp_id) as rn
from employee
) emp_new

-- 2) Fetch the top 3 employees in each department earning the max salary.

select * from employee

select *
from (
select *
, row_number() over(partition by dept_name order by salary desc) as rn
, rank() over(partition by dept_name order by salary desc) as rnk
from employee
) emp_new

-- DENSE_RANK:
select *
, row_number() over(partition by dept_name order by salary desc) as rn
, rank() over(partition by dept_name order by salary desc) as rnk
, dense_rank() over(partition by dept_name order by salary desc) as dn_rnk
from employee

-- Interview problem 1
/* Find the date with the highest total energy consumption from the Meta/Facebook data centers.
Output the date along with the total energy consumption across all data centers.
If there are multiple days with same highest energy consumption then display both dates.
*/
select * from fb_eu_energy;
select * from fb_asia_energy;
select * from fb_na_energy;

drop table fb_eu_energy;
create table fb_eu_energy
(
date date,
consumption int
);
drop table fb_asia_energy;
create table fb_asia_energy
(
date date,
consumption int
);
drop table fb_na_energy;
create table fb_na_energy
(
date date,
consumption int
);

insert into fb_eu_energy values ('2020-01-01',400);
insert into fb_eu_energy values ('2020-01-02',350);
insert into fb_eu_energy values ('2020-01-03',500);
insert into fb_eu_energy values ('2020-01-04',500);
insert into fb_eu_energy values ('2020-01-07',600);

insert into fb_asia_energy values ('2020-01-01',400);
insert into fb_asia_energy values ('2020-01-02',400);
insert into fb_asia_energy values ('2020-01-04',675);
insert into fb_asia_energy values ('2020-01-05',1200);
insert into fb_asia_energy values ('2020-01-06',750);
insert into fb_asia_energy values ('2020-01-07',400);

insert into fb_na_energy values ('2020-01-01',250);
insert into fb_na_energy values ('2020-01-02',375);
insert into fb_na_energy values ('2020-01-03',600);
insert into fb_na_energy values ('2020-01-06',500);
insert into fb_na_energy values ('2020-01-07',250);

-- SOLUTION
with cte as
(select * from fb_eu_energy
union all
select * from fb_asia_energy
union all
select * from fb_na_energy),
cte_agg as
(select date, sum(consumption) as total_consumption
, rank() over(order by sum(consumption) desc) as rnk
from cte
group by date)
select *
from cte_agg
where rnk = 1;