I Asked This SQL Interview Question in an Amazon Interview | Most Asked SQL Problem with a Twist

In this video we will discuss an SQL question to find 3rd highest salary employee in each department but there is a twist in the question. Check out the video for details.

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

script:
CREATE TABLE [emp](
[emp_id] [int] NULL,
[emp_name] [varchar](50) NULL,
[salary] [int] NULL,
[manager_id] [int] NULL,
[emp_age] [int] NULL,
[dep_id] [int] NULL,
[dep_name] [varchar](20) NULL,
[gender] [varchar](10) NULL
) ;
insert into emp values(1,'Ankit',14300,4,39,100,'Analytics','Female')
insert into emp values(2,'Mohit',14000,5,48,200,'IT','Male')
insert into emp values(3,'Vikas',12100,4,37,100,'Analytics','Female')
insert into emp values(4,'Rohit',7260,2,16,100,'Analytics','Female')
insert into emp values(5,'Mudit',15000,6,55,200,'IT','Male')
insert into emp values(6,'Agam',15600,2,14,200,'IT','Male')
insert into emp values(7,'Sanjay',12000,2,13,200,'IT','Male')
insert into emp values(8,'Ashish',7200,2,12,200,'IT','Male')
insert into emp values(9,'Mukesh',7000,6,51,300,'HR','Male')
insert into emp values(10,'Rakesh',8000,6,50,300,'HR','Male')
insert into emp values(11,'Akhil',4000,1,31,500,'Ops','Male')

#sql #dataengineer #interview

[nachiketpalsodkar]

Another Simpler Approach

;
WITH cte as (
select *
, DENSE_RANK() over(partition by dep_id order by salary DESC) as RNK
from emp )
, cte1 as (
select *
, MIN(salary)over(partition by dep_id ) as MINSAL
from cte
where RNK <= 3 )

select *
from cte1
where salary = MINSAL

[nishitnishikant]

with ct1 as
(select *, RANK() over(partition by dep_id order by salary desc) as rnk,
RANK() over(partition by dep_id order by salary) as rnk2,
count(*) over(partition by dep_id ) as cnt from emp)
,
ct2 as
(
select *,
case when ct1.cnt>=3 and ct1.rnk=3
then 1
when ct1.cnt<3 and ct1.rnk2=1
then 1
end flag1
from ct1
)
select ct2.emp_id, ct2.emp_name, ct2.salary, ct2.manager_id, ct2.emp_age, ct2.dep_id, ct2.dep_name, ct2.gender
from ct2 where ct2.flag1=1

[skkholiya]

select * from (
select *, count(emp_name) over(partition by dep_name) dept_emp,
rank() over(partition by dep_name
order by salary desc) rnk from emp
) sub_tbl where dept_emp>2 and rnk = 3 or dept_emp<3 and dept_emp=rnk

[SuperMohit]

2 years again, rewatching these videos for interview prep and came up with a different solution. I love SQL!

WITH cte AS(
SELECT
*,
COUNT(emp_id) OVER(PARTITION BY department_id) AS count,
DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC ) AS rn_desc,
DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary ASC ) AS rn_asc
FROM
emp
), salary AS(
SELECT
*,
CASE
WHEN count < 3 AND rn_asc = 1 THEN 1
WHEN rn_desc = 3 THEN 0
END AS flag
FROM
cte
)
SELECT
*
FROM
salary
WHERE
flag IN (0, 1)
;

[shahrukhtheanalyst]

Thanks for a brilliant twist Ankit, here is my take on the problem:


with cte AS
(
SELECT *, DENSE_RANK() over (partition by dep_id ORDER BY SALARY DESC) AS RN, COUNT(1) OVER (Partition by dep_id) AS CNT
FROM emp
)

SELECT * FROM cte WHERE RN = (CASE WHEN CNT >=3 THEN 3 ELSE CNT END)

[amumishra]

Dear Ankit. I came across your videos recently and joining a Faang company dream is up again. Thanks a ton for these wonderful videos. It would be really great if you can post videos on data modelling, dwh must know concepts as I am not finding any good course on data modelling. Also on usage of surrogate keys, database designs etc

[mohitupadhayay]

Such phenomenal stuff!
Not a day goes by when I'm not learning from you bhai!
Kudos to you!

[manojsrikanth]

Hi Ankit, Thank you for posting such a lovely question. Here's my result without seeing your answer.
select * from
(select *, dense_rank() over (partition by dep_name order by salary desc) dr,
row_number() over (partition by dep_name order by dep_id) rn from emp1) p
where dr = 3 or salary in
(select min(salary) over (partition by dep_name order by salary) from emp1 where rn<3)

[rahulbansal]

Another solution is to use simply fetch rows having rank less than 3

with temp_table as (
select emp_name, dep_name, salary, DENSE_RANK() over (partition by dep_name order by salary desc) as rank_order from emp
), temp_table2 as
( select emp_name, dep_name, salary, rank_order, DENSE_RANK() over (partition by dep_name order by rank_order desc) as real_rank_order from temp_table where rank_order <= 3
)


select * from temp_table2 where real_rank_order = 1

[kartikpidurkar]

Hi Ankit, in case where the number of employees are less than 3, it might be the case that they have same salary, rank will be 1 for both, then rank i.e 1 <> count i.e 2. Instead we should use union all to consider these cases separately.

[dasoumya]

hello! ankit my solution is

with cte1 as(select *, max(drank)over(partition by dep_name) as max_drank
from(select *, dense_rank()over(partition by dep_name order by salary desc)as drank
from emp) as A
where drank <=3 )

select emp_id, emp_name, salary, dep_name
from cte1
where max_drank=drank

[shekharagarwal]

Thanks Ankit and learned your twist . Really Like your simplified approach
with cte1 as
(
select *
, rank() over ( partition by dep_id order by salary desc) rnk
, count(1) over ( partition by dep_id) as total_emp
from emp
)
Select * from cte1
where rnk = case when total_emp>=3 THEN 3
when total_emp<3 THEN total_emp
end

[sahilummat]

Amazing Video

another solution from my end

with cte as (
select *
, row_number()over(partition by dep_id order by salary desc) as rn_max_sal,
row_number()over(partition by dep_id order by salary ) as rn_min_sal,
count(1)over(partition by dep_id) as number_of_emp
from employee)
select * from cte
where (number_of_emp >=3 and rn_max_sal=3) or (number_of_emp<3 and rn_min_sal=1)

order by emp_id;

[gouravkumar]

Amazing solution Ankit bhai great work !!!

[srivatsan]

with cte as (
select *
from emp),
cte2 as (
select *,
RANK() over (partition by dep_id order by salary desc) as rn,
COUNT(*) over (partition by dep_id) as cnt
from cte)
select emp_id, emp_name, salary, manager_id, emp_age, dep_id, dep_name, gender
from cte2
where (rn =3 and cnt >=3) or (rn=cnt and cnt<=3)

[KoushikT]

Found a simpler approach using coalesce 😁😁😁😁😁😁😁😁😁😁😁😁

with A as (
select *,
coalesce(nth_value(salary, 3) over (partition by dep_name order by salary desc), min(salary) over (partition by dep_name)) as third_sal from emp)

select emp_id, emp_name, salary, dep_id, dep_name
from A
where salary=third_sal

[rohanchoudhary]

with cte_rank_sal as (
select
*,
rank() over(partition by dep_id order by salary) as rn
from emp
), cte as (
select
*,
max(rn) over(partition by dep_id) as max_rn
from cte_rank_sal
)
select emp_name, salary, rn, dep_id from cte
where rn = 3 or (max_rn < 3 and max_rn = rn)

[asifnawaz]

My simple approach

Select emp_id, emp_name, salary, manager_id, emp_age, dep_id, gender from (
Select *,
ROW_NUMBER() over(partition by dep_id order by dep_id, salary desc) row_n
, count(1) over(partition by dep_id) cnt
from emp) s
where cnt>=3 and row_n=3
or cnt=2 and row_n=2
or cnt = 1 and row_n =1
;

[abhishek_grd]

Perfect one ! Solved by myslef but took close to 10 minutes. Awesome question...

[subodhthore]

with cte as (select *, dense_rank()over(partition by dep_name order by salary desc) as dr from emp1),
cte1 as (select dep_name from cte
group by dep_name having max(dr)<3 )
,

cte2 as (select emp_id, dep_name, row_number()
over(partition by dep_name order by salary asc) as rn
from cte where dep_name in( select * from cte1) ),

cte3 as (
select emp_id from cte2 where rn=1)


select * from cte where dr=3 || emp_id in(select emp_id from cte3) ;