Bayer SQL Interview Question-Dept wise 2nd Highest Salary using DENSE_RANK() and Correlated Subquery

[@tarungangadhar14]

with cte as(
select empid,salary,e.deptid,t.deptname
from ele e inner join dt t on e.deptid = t.deptid),
cte2 as(
select *,DENSE_RANK() over(partition by deptname order by salary desc) as ord
from cte)
select empid,deptname,salary from cte2 where ord =2

[@SaifKhan-sd2gd]

with cte as (
select *, dense_rank() over(partition by e.deptid order by e.salary desc) as rnk
from employee e
join department d
on e.deptid=d.deptid
)
select deptname, empid, salary from cte
where rnk=2

[@ashwingupta4765]

with cte as(
Select *,
dense_rank() over(partition by deptid order by salary desc) as rnk
from employee)
,cte2 as (
select * from cte
where rnk =2)
Select d.deptname, c2.empid, c2.salary from cte2 as c2
inner join
department as d
on
c2.deptid = d.deptid

[@prajju8114]

with cte_secondhighest as
(
Select d.deptname,e.empid, e.salary, dense_rank() over(partition by d.deptid,d.deptname order by e.salary desc) as rn from employee e,department d where e.deptid=d.deptid
)
Select * from cte_secondhighest where rn=2

[@devarajululanka6427]

Here is my solution :
with cte as (
select e.empid,e.salary,deptname,d.deptid,max(salary) as max_salary from employee e
inner join department d
on
e.deptid = d. deptid
group by e.empid,e.salary,d.deptname,d.deptid ),
drn_salary as (select *,
DENSE_RANK() over (partition by deptname order by max_salary desc) as drn
from cte )
select deptname,empid,salary from drn_salary
where drn = 2
order by empid

[@iamram436]

with cte as(select d1.deptname as deptname,e1.empid as empid,e1.salary as salary,
dense_rank() over(partition by e1.deptid order by e1.salary desc ) as dns
from department_1 d1
inner join employee_1 e1
on d1.deptid=e1.deptid)
select * from cte where dns=2

[@Srikanth-mz9nl]

Thank you !

[@CloudChallengers]

@Srikanth-mz9nl, Thanks for liking. Keep watching upcoming videos as well.

[@sravankumar1767]

Nice explanation

[@CloudChallengers]

@sravankumar1767, Thanks for liking. Keep watching upcoming videos as well.

[@HARSHRAJ-gp6ve]

with cte as(
select empid,salary,deptid FROM(select employee.*,DENSE_RANK()OVER(PARTITION BY deptid ORDER BY salary DESC) as x1 FROM
employee) as es where es.x1=2
)
select department.deptname,empid,salary FROM cte JOIN department ON cte.deptid=department.deptid;

[@arindamnaskar671]

Sir Can you please explain this is pyspark

[@CloudChallengers]

@arindamnaskar671, Check the below pyspark code.
from pyspark.sql.functions import dense_rank
from pyspark.sql.window import Window
windowSpec = Window.partitionBy("deptname").orderBy(employee_df["salary"].desc())
ranked_employee_df = employee_df \
.join(department_df, employee_df.deptid == department_df.deptid) \
.select(employee_df["*"], department_df["deptname"], dense_rank().over(windowSpec).alias("rank"))
result_df = ranked_employee_df.filter(ranked_employee_df["rank"] == 2)
result_df.display()

[@vaibhavkhot2675]

If only one record I dept then

[@CloudChallengers]

@vaibhavkhot2675, we don't get any record for that department then.

[@sportsblast8215]

with cte as (select dt.deptname,ey.empid,ey.salary,dense_rank()over(partition by dt.deptname order by ey.salary desc) as b from dt join ey on
dt.deptid=ey.deptid )
select * from cte where b=2 order by cte.empid