SQL Interview Question for Senior Data Engineer Position in Poland | Data Engineering

In this video we are going to discuss a SQL interview problem asked in epam systems interview for senior data engineer position. We are going solve this with inner join and left join both.
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
script:
CREATE TABLE [emp_salary]
(
[emp_id] INTEGER NOT NULL,
[name] NVARCHAR(20) NOT NULL,
[salary] NVARCHAR(30),
[dept_id] INTEGER
);
INSERT INTO emp_salary
(emp_id, name, salary, dept_id)
VALUES(101, 'sohan', '3000', '11'),
(102, 'rohan', '4000', '12'),
(103, 'mohan', '5000', '13'),
(104, 'cat', '3000', '11'),
(105, 'suresh', '4000', '12'),
(109, 'mahesh', '7000', '12'),
(108, 'kamal', '8000', '11');
#sql #dataengineer #interview

[@shivamsingla7596]

select a.name
from emp_salary as a
join emp_salary as b
on a.salary=b.salary and a.dept_id=b.dept_id and a.name b.name

[@hamdaniftikhar]

Hi Ankit. Another interesting problem. Thank you so much for sharing these!
This is my solution with a window function (without joins) -
WITH cte AS (
SELECT *,
COUNT(*) OVER (PARTITION BY dept_id, salary) num_same_sal
FROM emp_salary
)
SELECT *
FROM cte
WHERE num_same_sal > 1

[@prashantmhatre9225]

I have tried this easy as well and working fine. Thanks

[@saidev2568]

I exactly had the same thought on in my mind after reading this question

[@inspiredomkar1239]

Thanks man.This is the easiest solution

[@vaibhavtiwari8670]

Good method i also created like this but i was using rank function and in my query the issue i was getting that mohan name too your solution helped me alot good query 👏👏👏👏👏👏

[@vinothkumars7421]

This feels easy and understandable. Thanks

[@mayankbhardwaj1487]

select a.emp_id,a.name,a.salary,a.dept_id from emp_salary a
inner join emp_salary b
on a.dept_id=b.dept_id and a.emp_idb.emp_id
where a.salary=b.salary
order by a.dept_id

[@Reacher1998]

Hi Ankit..Thanks for the problem...Here'e my solution..
with cte as(select *,dense_rank() over (partition by dept_id order by salary) as rnk
from emp_salary)
select a.emp_id,a.name,a.salary,a.dept_id
from cte a, cte b where a.emp_idb.emp_id and a.dept_id=b.dept_id and a.rnk=b.rnk;

[@kailashpatro5768]

hi ankit i am working in amazon non technical back ground but i am having interest to solve SQL problems then i started learning sql by my own and i am searching sql videos one fine day i have seen your sql videos then i fall love with your SQL videos still now i am seeing yours sql videos and now i am solve sql problems in my own that much impact you created to me thank you so much ankit if it is possible defiantly i will meet you one day
problem solution
select emp_id,name,salary,dept_id from (
select *,
count(1) over(partition by dept_id order by salary) as no_of_times from emp_salary
) a
where no_of_times = 2

[@ls47295]

Thanks Ankit for interesting problem.
My solutions :
solution 1:
with cte_rank as (
select a.*,
dense_rank()over( partition by dept_id order by salary asc) as rnk
FROM emp_salary a
)
select c.*
from cte_rank c Inner JOIN cte_rank d
on c.dept_id = d.dept_id
AND c.rnk = d.rnk
and c.emp_id d.emp_id;
solution 2:
select c.*
from emp_salary c Inner JOIN emp_salary d
on c.dept_id = d.dept_id
AND c.salary = d.salary
and c.emp_id d.emp_id;

[@reshmashaik6606]

select a.name, a.salary, a.dept_id from emp_salary a, emp_salary b
where a.nameb.name and a.salary=b.salary and a.dept_id=b.dept_id
order by a.dept_id

[@surajn4902]

select distinct a.emp_id,a.name,a.salary,a.dept_id from (
(select * from emp_salary order by dept_id )a inner join emp_salary b on a.emp_idb.emp_id and a.dept_id=b.dept_id and a.salary=b.salary)
order by a.dept_id

[@swtannie19]

Thank you, Ankit. This is my solution to the problem :
select a.* from emp_salry a
join emp_salry b
on a.emp_id b.emp_id and a.dept_id = b.dept_id
where a.salary =b.salary

[@hemantchaudhary591]

Using self join.

[@saidev2568]

This is also a way in which I solved it apart from a window function

[@kameshmuthu1566]

Hi am new for SQL , now am learning. Compare with all solution i feel your answer really simple and understandable. Can i learn sql from you ?

[@saritha3991-o4j]

Hi Ankit,
This is my Approach
select a.* from emp_salary a
join
emp_salary b
on a.emp_idb.emp_id
where a.salary=b.salary and a.dept_id=b.dept_id
order by a.salary;

[@Brown_Munda_oo7]

With LEFT JOIN :- SELECT A.*
FROM emp_salary AS A
LEFT JOIN emp_salary AS B
ON (A.dept_id = B.dept_id AND A.salary = B.salary) AND A.emp_id B.emp_id
WHERE B.emp_id IS NOT NULL
WITH INNER JOIN :- SELECT A.*
FROM emp_salary AS A
INNER JOIN emp_salary AS B
ON (A.dept_id = B.dept_id AND A.salary = B.salary) AND A.emp_id B.emp_id

[@amitpatil7721]

select a.* from emp_salary a , emp_salary b
where a.salary=b.salary and a.nameb.name
order by dept_id

[@nikhilreddy4582]

Hi ankit! really helpful videos and here's my solution ( select * from(select *,count(rn) over(partition by rn) as same from(select *,rank() over(order by dept_id, salary)rn from emp_salary)a)b
where same = 2 )

[@venkataram6460]

Hi Ankit,
I have solved it using self join
select t1.*
from emp_salary t1
inner join emp_salary t2 on t1.emp_id != t2.emp_id and t1.dept_id = t2.dept_id and t1.salary = t2.salary

[@Kumar_b_Sushil]

select emp_id,name,salary,dept_id from (select emp_id,name,salary,dept_id, count(salary) over(partition by salary order by dept_id) repet from emp_salary) as A
where repet >1;

[@rajanverma97]

APPROACH 1 : (CTE)
with eligible as (
select salary,dept_id,count(1) as cnt from emp_salary
group by salary,dept_id
having count(1)>1
)
select e.* from emp_salary e join
eligible l
on e.salary=l.salary and e.dept_id=l.dept_id
APPROACH 2 : (Inner Join)
select e1.* from emp_salary e1
join emp_salary e2
on e1.dept_id=e2.dept_id and e1.salary=e2.salary and e1.emp_id!=e2.emp_id
Thank you Ankit for sharing

[@ankitbansal6]

In the second approach you may have duplicates if there are more than 2 employees with the same salary in the same department

[@rajanverma97]

@@ankitbansal6 That's true, thanks for correcting

[@NikhilGarg-uo3ps]

Thank you Sir!
My Solution:
select e.* from emp_salary e
inner join (select dept_id,count(1) cnt,salary from emp_salary
group by dept_id,salary
having count(1)>1) as x on e.dept_id = x.dept_id and e.salary = x.salary

[@tamijuddinaymadar9674]

select a.emp_id,a.name,a.dept_id,a.salary from #emp_salary a join #emp_salary b on a.dept_id=b.dept_id where
a.emp_idb.emp_id and a.salary=b.salary and a.dept_id=b.dept_id order by dept_id

[@sahilgarg7226]

Hi Ankit. This is my solution -
select a.* from emp_salary a
join emp_salary b on (a.dept_id = b.dept_id and a.emp_id b.emp_id and a.salary = b.salary)

[@dgsharma4773]

select emp_id,
name,
salary,
dept_id from(
select *,
count(1) over (partition by dept_id order by salary) as rn
from emp_salary
)x
where x.rn = 2
order by emp_id

[@Artouple]

This is a simpler solution, i think : P
select a.* from
emp_salary a
inner join emp_salary b
on a.dept_id = b.dept_id
and a.salary = b.salary
and a.name b.name
order by dept_id;

[@akshaynagargoje6389]

Select a.* from emp_salary a
Left Join emp_salary b on a.salary=b.salary AND a.emp_id b.emp_id
where b.emp_id is NOT NULL

[@dhrumilgohel1655]

Got this solution in mind after watching question and output:
select * from emp_salary e
where e.salary in (select salary from emp_salary ee where ee.dept_id = e.dept_id and e.emp_id != ee.emp_id)
order by e.dept_id;
can some check if its correct or not as for me its giving right answer but need to make sure if i am not missing any corner cases.
Thanks Sir for such an great video!!!

[@JupudiRamaDurgaPrasad]

Hi sir, really this is wonderful...
but, instead of Group by and CTE...., we can get the output by simply self join
select A.emp_id, A.salary
from akb6 A, akb6 B
where A.emp_id B.emp_id and A.salary = B.salary |
I think it's working fine....

[@aaravkumarsingh4018]

select * from emp_salary e where salary in(select salary from emp_salary where e.dept_id=dept_id and e.name !=name) order by dept_id;

[@kunwarabhinavpratapsingh8918]

Hi Ankit, this was an interesting approach. However, I was thinking of a way to do it without joins and came up with a solution that uses multiple CTEs.
with cte as (select *,DENSE_RANK() over (order by salary desc) as ss from emp_salary),
cte2 as (select ss,count(*) from cte group by ss having count(*) >1)
select emp_id,name,salary,dept_id from cte where ss in (select ss from cte2) order by dept_id;

[@ShivamGupta-wn9mo]

using windows function
WITH cte AS (
SELECT
*,
COUNT(*) OVER (PARTITION BY dept_id, salary ) AS cnt
FROM
emp_salary
)
SELECT
*
FROM
cte
WHERE cnt > 1;

[@manojsrikanth8663]

Hi Ankit, Thank you for uploading very interesting videos.. Everytime I will try to get ans myself then I will look into your ans.Here is my ans..
select emp_id, name,salary,dept_id from
(select emp_id, name,salary,dept_id,count(cn) over (partition by cn) rn from
(select *, salary+dept_id cn from emp_salary order by dept_id) p ) q where rn 1

[@kasmitharam982]

I solved this using the window function, I'm not sure if this was a fluke or if I'm getting better by watching your videos.
This is the first solution that came to my mind.
My solution:
WITH CTE AS (SELECT * ,
count(*) over(partition by dept_id, salary) as same_salary
FROM emp_salary)
SELECT emp_id, name, salary, dept_id
FROM CTE
WHERE same_salary > 1;

[@ankitbansal6]

Brilliant. You are getting better 👏

[@imnishantm]

I think this will not work if we have one more row with dept id-13 and different salary

[@AnimeAnimal.]

hi sir for this problem
and my answer is
with Shiv as
(
Select salary, count(salary) as salary_detais , dept_id,count(dept_id) as dept_details From emp_salary
group by salary ,dept_id
)
Select name From emp_salary
left join Shiv on emp_salary.salary =Shiv.salary
Where salary_detais ='2' and dept_details ='2'

[@himankkhandelwal741]

Hi Ankit,
My solution:
Select es1.*
from emp_salary es1
left join emp_salary es2
on es1.salary = es2.salary and es1.dept_id = es2.dept_id and es1.name!= es2.name
where es2.name is not null
order by dept_id

[@vishalchakraborty2050]

Hi Ankit,
Thank you so much for sharing these!
This is my solution using inner join
select e1.* from emp_salary as e1
inner join
emp_salary as e2
on e1.emp_id != e2.emp_id
and e1.salary =e2.salary
and e1.dept_id=e2.dept_id
order by e1.salary,e1.salary;

[@saralavasudevan5167]

Hi Ankit. This is my solution without joins/window function:
select * from emp_salary
where salary in (select salary from emp_salary
group by salary
having count(1) >1)
order by dept_id

[@harshvardhan8534]

Hi Ankit thank you for sharing this problem, this is my solution:-
select A.empl_id,A.empl_name,A.dept_id,A.salary from (select empl_id,empl_name,dept_id,salary,dense_rank() over(partition by dept_id order by salary desc) as rn from employee1) as A cross join (select empl_id,empl_name,dept_id,salary,dense_rank() over(partition by dept_id order by salary desc) as rn from employee1) AS B where A.dept_id=B.dept_id and A.rn=B.rn and A.empl_id!=B.empl_id order by empl_id,dept_id;

[@abhinavsingh1173]

Hi Ankit What about below Solution. I think is better
With my_cte AS (
Select *, count(*) over (partition by dept_id,salary) as cnt from emp_salary)
Select emp_id, name, salary, dept_id from my_cte Where cnt>1 order by dept_id asc;

[@SachinKumar-lz9en]

with cte as(
select EMP_ID, NAME, SALARY, DEPT_ID
,Dense_rank() over(partition by DEPT_ID order by SALARY desc) rnk
from emp_salary)
select * from cte
where RNK=2

[@RAJBHALANI]

Another Approach using Self Join :
select e1.* from emp_salary e1 inner join emp_salary e2 on e1.emp_id e2.emp_id and e1.dept_id = e2.dept_id and e1.salary = e2.salary

[@hardikjain1528]

hi ankit
this is my approach towards solution;
select emp_id,name,salary,dept_id from (
select *, dense_rank() over (partition by dept_id order by salary desc) as rnk
from emp_salary ) b
where rnk = 2

[@blitzhope]

I think this approach will give the 2nd highest salary in each department..right?

[@AmrutaKhot-n3j]

select c1.*
from emp_salary c1
inner join emp_salary c2 on c1.dept_id = c2.dept_id and c1.salary = c2.salary
where c1.emp_id c2.emp_id
order by dept_id;

[@GauravMalhotra-cm1re]

Hi Ankit. Here's my solution to this using LEAD() and LAG() window functions :-
with cte1 as
(
select *,
lead(salary) over (partition by dept_id order by salary) as "lead_salary",
lag(salary) over (partition by dept_id order by salary) as "lag_salary"
from emp_salary
)
select *
from cte1
where salary = lead_salary or salary = lag_salary;

[@adrenaline_strength3113]

Hi ankit thanks for all this queries section they are helping me a lot for my career growth i have same without cte
---------------------------------------
select * from (select *, count(1) over(partition by dept_id , salary) number from emp_salary )as emp where number>1

[@swathijaiganesh4803]

my approach :select e1.emp_id,e1.name , e1.dept_id,e1.salary from employees e1
inner join employees e2
on e1.dept_id = e2.dept_id
and e1.salary = e2.salary
where e1.emp_id e2.emp_id

[@dileep3809]

Thanks Ankit for answering it
...I was running out of time in interview and couldn't think much...Thankyou for taking your time and answering upon my request

[@ankitbansal6]

👍

[@dileep3809]

@@ankitbansal6 Hi Ankit, 3 questions asked for amazon business analyst...Rest all already present in your channel. please make a video on these too..it would be helpful
1. Table as emp_id ,mgr_id (only 2 columns) but return 3 columns emp_id,mgr_id and senior_mgr_id(manager of manager_id)
2.Table College marks - student, subject, dept, marks
write a query which gives dept, sub, Average marks per subject, avg marks per dept
3.Call center data - call id, call in time, agent id, customer id, Feedback Rating (1-10) has NULLs also in this column
* Write a query which gives agent_id, month, percentages of positive, negative, nuetral feedbacks. Exclude NULL records

[@sushanthsai2078]

hi sir , from the result set seems the below sql is also working
select * from emp_salary a
inner join emp_salary b
on b.name!=a.name and b.salary = a.salary and b.dept_id = a.dept_id
order by a.emp_id;

[@anmolpawa]

Hi Ankit sir,
This is my solution for this problem.
select emp1.*
from emp_salary emp1
inner join emp_salary emp2
on emp1.dept_id = emp2.dept_id
and emp1.salary = emp2.salary
and emp1.emp_id != emp2.emp_id
Thank you so much for sharing this

[@raghuveersangashetty4845]

Another approach using in clause:
select *
from emp_salary
where (dept_id,salary) IN (
select dept_id,salary
from emp_salary
group by dept_id,salary
having count(*) > 1
);

[@ankitbansal6]

Yes a good one but this kind of in clause doesn't work in SQL server

[@raghuveersangashetty4845]

@@ankitbansal6 It works in Oracle

[@anuragkumar-ti1vz]

solution using self join
select
e1.*
from emp_salary as e1
inner join emp_salary as e2
on e1.dept_id = e2.dept_id and e1.salary = e2.salary and e1.emp_id e2.emp_id
order by e1.dept_id

[@VARISHROCKS]

Hi !
Thankyou for Sharing , Below is my solution :
with cte1 as (select dept_id,salary,emp_id,emp_name, count(1) over(partition by dept_id , salary) as ct from emp1)
select emp_id,emp_name,salary,dept_id from cte1 where ct>1

[@dakshbhatnagar]

select emp_id, name, dept_id, salary from
(select *, count(*) over(partition by dept_id) as count,
dense_rank() over(partition by dept_id
order by salary)%2 as rn from emp_salary)
where rn = 1 and count > 1

[@mayanksatija684]

Simple and easy way :
with t1 as (
select salary from (
select salary,dept_id from emp_salary group by salary,dept_id having count(*) > 1))
select * from emp_salary where salary in (select * from t1)

[@sharu164]

Thanks, Ankit I really appreciate your efforts in making these videos.

[@TanyaWadhwa-u4o]

with base as (
select dept_id,salary from emp_salary
group by dept_id,salary having count(*) > 1)
select * from emp_salary where dept_id in (select distinct dept_id from base) and salary in (select distinct salary from base)

[@arshadmohammed2346]

PostgreSql:
Using Lag and Lead functions
with cte as (
select * ,
lead(salary) over( partition by salary, dept_id order by salary ) as rlead ,
lag(salary) over( partition by salary,dept_id order by salary ) as rlag from emp_salary
)
select emp_id, name, salary from cte where salary in (rlag, rlead)

[@susantsahoo5853]

In Spark SQL
select *,
count(1) over(partition by dept_id,salary order by dept_id) cnt
from emp_salary
qualify cnt>1

[@kunalprakash7826]

I did using this method :
select distinct e1.emp_id, e1.name, e1.salary, e1.dept_id
FROM emp_salary e1
inner join emp_salary e2
using(salary, dept_id) where e1.emp_id != e2.emp_id;

[@NitishChaubey-j5y]

with cte as A(
Select *,case when salary= lead() over (partition by dept id order by emp id)
then 1 else 0 end as flag)
Select empID,Name,salary,dept_ID from A
where emp id in (Select emp id when flag=1 from A)

[@Hope-xb5jv]

another way
-----1st way
with cte as
(
select *,row_number()over(partition by salary,dept_id order by emp_id) r from emp_salary
) select emp_id,name,salary,dept_id from cte
where salary in (select salary from cte where r > 1)
order by salary,emp_id
------2nd way
select * from emp_salary
where salary in (select salary from emp_salary
group by salary,dept_id
having count(*) > 1)

[@puneetnaik8719]

with CTE as(
select salary, dept_id from emp_salary group by salary, dept_id having count(salary)>1)
select emp_id,name, e.salary from emp_salary e join cte c on e.salary=c.salary and
e.dept_id=c.dept_id ;

[@prashantmhatre9225]

Thanks a lot bhai , bhai only one suggestion ,in many videos what I have observe is some time you are initially declaring how you are going to solve the query and then explain the scenario. This is also good but as a viewer I already know the approach and we also start thinking in that way . So if you first explain the scenario and then we will pause the videos and also think about various approaches and tried by ourself and if we stuck then definatly we will follow your approach only .

[@ganeshmoorthy816]

Even I have the same suggestion. The moment we hear the approach, it's difficult to think of other options.so please consider this .

[@_Samridhijain_]

select
dept_id,
name,
salary
from
(
select
dept_id,
name,
salary,
coalesce(salary - Lag(salary) over (partition by dept_id order by salary rows between unbounded preceding and current row ),0) as diff ,
count(dept_id) over (partition by dept_id,Salary ) as cnt
from emp_salary_ank
) a
where diff =0 and cnt > 1

[@pranaygirde7299]

select * from emp_salary
where salary in (select salary from emp_salary group by salary,dept_id having count(*) > 1) and
dept_id in (select dept_id from emp_salary group by salary,dept_id having count(*) > 1)
order by emp_id
This also works 😁

[@anthonychilaka]

Day 5 of 47
I have put this video on repeat so I can grasp the concept of the Inner join or Left join solution for this use case.
Thanks Ankit

[@ashishjawrani5013]

Here are my solutions
1. Using Joins
select a.emp_id, a.name, a.salary, a.dept_id from
emp a join emp b
on a.nameb.name
and a.salary=b.salary
and a.dept_id=b.dept_id
order by a.dept_id
2. Using Window function
with cte as (
select emp_id,name, dept_id, salary,
count(*) over (partition by dept_id,salary order by salary) as cnt
from emp
)
select * from cte where cnt>1

[@sakshinaik05]

thank you for bringing us a mind boggling question

[@udhhavarora5483]

How about this?
select a.*
from emp_salary a
inner join emp_salary b
on a.salary = b.salary and a.dept_id = b.dept_id and a.emp_id b.emp_id
order by dept_id

[@udhhavarora5483]

ah, I see someone has already shared this query. :)

[@amanbhardwaj7582]

with cte as (
SELECT emp_id,dept_id,name,salary,
Dense_rank() over (partition by dept_id ORDER by salary) as rnk ,
count(*)over (partition by salary) as cnt
FROM emp_salary
)
select emp_id,dept_id,name,salary
from cte
where cnt> 1 and rnk = 1

[@LS8636]

Hey Ankit thanks for sharing this amazing information. Keep sharing knowledge and keep growing 👍

[@ankitbansal6]

Thank you 😊

[@karpagammohan6605]

Hi Ankit, Thank you so much! I tried different query.
with same_dept as
(select dept_id,salary,count(emp_id) as emp_cnt from emp_salary group by dept_id,salary having count(emp_id)>1)
select * from emp_salary e join same_dept sd on e.dept_id = sd.dept_id and e.salary= sd.salary

[@Tech_with_Srini]

My approach:
select e1.* from emp_salary e1 , emp_salary e2
where e1.salary=e2.salary and e1.dept_id=e2.dept_id and e1.emp_id

[@atharvabhangre9044]

My solution
SELECT * FROM
( SELECT *, COUNT(*) OVER(PARTITION BY DEPT_ID, SALARY) AS Sal_count
FROM EMP_SALARY ) a
WHERE Sal_count > 1;

[@RamiKaur-n7h]

my solution is to slove this
select * from emp_salary where (salary, dept_id) in ( select salary, dept_id from emp_salary
group by salary, dept_id
having count(*) >1)

[@anupgupta5781]

Beautiful question and you made it easy Ankit sir

[@raghuveersangashetty4845]

Here is my approach using count windows function:
with salary_frequency as (
select emp_id,
name,
salary,
dept_id,
count(salary) over(partition by dept_id,salary order by salary) as dept_sal_count
from emp_salary
order by dept_id,salary
)
select emp_id,name,salary,dept_id
from salary_frequency
where dept_sal_count > 1;

[@shankarpesala921]

select * from emp_salary where concat(dept_id,salary) in
(select c from (
select concat(dept_id,salary) c,count(concat(dept_id,salary)) c1 from emp_salary group by concat(dept_id,salary)
having count(concat(dept_id,salary))>1) x) order by salary

[@CEMANIVANNAN]

select * from emp_salary where (salary,dept_id) in
(select salary,dept_id from emp_salary group by salary,dept_id having count(*)>1) order by dept_id;
select t1.* from emp_salary t1 join emp_salary t2 on
t1.dept_id=t2.dept_id and t1.salary=t2.salary
where t1.emp_idt2.emp_id;

[@ujjwalvarshney3188]

select * from
(
select * ,count(*)over( partition by dept_id ,salary ) as rk from emp_salary
) where rk =2

[@aspiringdatascientist4163]

select emp_id,name,salary, dept_id from "backup".emp_salary
where (salary, dept_id) in (
select salary,dept_id from "backup".emp_salary
group by salary,dept_id
having count(*) > 1
);

[@prabhatgupta6415]

select * from emp_salary where salary in (select a.salary from emp_salary a join emp_salary b on a.salary=b.salary and a.dept_id=b.dept_id and a.emp_id

[@prabhatgupta6415]

select * from emp_salary where (dept_id,salary) in (select dept_id,salary from emp_salary group by 1,2 having count(*) >=2) order by 4;

[@vamshireddy1183]

select a.emp_id,a.name,a.salary,a.dept_id from
(select * from emp_salary) as a
join (select * from emp_salary) as b
on a.dept_id = b.dept_id and a.emp_id!=b.emp_id and a.salary = b.salary
order by a.dept_id;

[@NamanSeth1]

Solution 1:
Hi Ankit. Thank you for the great content. Below are my solutions.
=========================
select e1.*
from emp_salary e1
inner join
emp_salary e2
on
e1.salary = e2.salary
and
e1.dept_id = e2.dept_id
and
e1.emp_id e2.emp_id
order by e1.dept_id
=====================================================
Solution 2:
with sal_rank as(
select *, DENSE_RANK() over (partition by dept_id order by salary ) rank from emp_salary
),
rank_count as(
select *, count(rank) over (partition by dept_id,rank) rank_wise_count from sal_rank
)
select emp_id,name,salary,dept_id from rank_count where rank_wise_count>1

[@ankitbansal6]

Thanks for posting. Good ones 🙂

[@arghyakar1992]

Best ans inner join

[@roshniagrawal4777]

select * from (select
emp_id,
name,
salary,
dept_id,
COUNT(*) OVER (PARTITION BY dept_id, salary) AS cnt
from
emp_salary)
where cnt >1 -- this works too

[@tamojeetchatterjee9385]

Hi Ankit . Very interesting problem
This is my solution using self join
select e1.emp_id as id , e1.name as name , e1.salary as salary ,
e1.dept_id as dept_id
from emp_salary as e1
join
emp_salary as e2
on e1.name e2.name and e1.salary = e2.salary
order by e1.dept_id , name desc

[@ank_kumar12]

with cte as
(select * ,
count(emp_id) over(partition by dept_id,salary) as no_of_employees,
count(emp_id) over(partition by dept_id) as cnt
from emp_salary
)
select emp_id, name, salary, dept_id from cte
where (cnt > 1) and (no_of_employees > 1);

[@karangupta_DE]

-- gives the output as per the video
with cte as (
select dept_id, salary, count(*) as total
from emp_salary
group by 1,2
)
select a.dept_id, a.salary, b.emp_id, b.name from (
select dept_id, salary from cte
where total > 1
) a
join
(select dept_id, salary, emp_id, name from emp_salary) b
on
a.dept_id = b.dept_id
and
a.salary = b.salary;
-- using self join concept
with cte as (
select *,
dense_rank()over(partition by dept_id order by salary desc) as rn
from emp_salary
)
select c.name, c1.name, c.salary, c.dept_id, c1.dept_id from cte c
join
cte c1
on
c.rn = c1.rn
and
c.dept_id = c1.dept_id
and
c.name > c1.name;

[@ankitbansal6]

Good attempt. But it can be simplified.

[@ashwingupta4765]

using windows function
with cte as (
select *,
rank() over(partition by dept_id order by salary) as ranker,
count(*) over(partition by dept_id order by salary) as counter
from emp_salary
)
Select * from cte
where counter > 2

[@yashmittal6605]

Sir, it would be great if you could share the Snapshot of the O/P data set in the beginning of the video itself. This will help us attempt the question on our own first. Thanku for all the efforts you are doing for the SQL community.

[@ankitbansal6]

I always do that. Will make sure I don't miss it going forward.

[@cemanivannan27]

select * from emp_salary where (salary,dept_id) in(
select salary,dept_id from
(select *,row_number() over(partition by dept_id,salary order by null) rn from emp_salary
)where rn>1);
select * from emp_salary where (salary,dept_id) in
(select salary,dept_id from emp_salary group by salary,dept_id having count(*)>1)
order by salary,dept_id;

[@srinivascheemala1287]

with cte as (
select emp_id,name,salary,dept_id,dense_rank() over (partition by dept_id order by dept_id,salary desc rows between unbounded preceding and current row)as rk from emp_salary )
select emp_id,name,salary,dept_id from cte where rk>1;

[@ashwingupta4765]

with cte as (
select *,
dense_rank() over (partition by dept_id order by salary desc) as rnk
from [dbo].[emp_salary]
group by dept_id,salary,emp_id,name
)
select distinct(c.emp_id), c.name , e.salary ,e.dept_id from cte as c
inner join
emp_salary as e
on
c.dept_id = e.dept_id
and
c.salary=e.salary
and
e.emp_idc.emp_id
order by e.dept_id

[@supreethalahari.sreeram5147]

Hi Ankit, Nice problem
my solution with out cte,joins
select group_concat(name) as emp_names ,salary,dept_id from emp_salary
group by dept_id,salary
having count(*) > 1

[@shivarajhalageri2513]

(Gp by dept id order by salary) left join this with source table a. Salary=b.salaty and a. Emp id! =b.emp id thenn from here pick up entries that are not null that gives us the soln. 🙌

[@shivarajhalageri2513]

One more method is by using window function,,, rank ie (partition by dept id order by salary assign rank) as rnk now on this apply gp by on rank and count rank and then take out entries where count >1.🙌🪄

[@ishwarkokkili7646]

Using self Join :
SELECT e1.emp_id, e1.name from emp_salary e1 left join emp_salary e2 on
e1.dept_id = e2.dept_id and e1.salary = e2.salary and e1.emp_id e2.emp_id
WHERE e2.emp_id is not null

[@rajendramaharjan5018]

with cte as
(
select
*,
count(1)over(partition by salary,dept_id) as cnts
from
#emp_salary
)
select
emp_id,
name,
salary,
dept_id
from cte
where cnts>1

[@AbhishekGupta-zf8tj]

Select * from (
Select name,emp_id, dept_id,salary,count(emp_id) over(partition by dept_id,salary) as w from emp_salary
) A where A.w > 1

[@santhanabharathip7808]

select e1.* from emp_salary e1, emp_salary e2
where e1.namee2.name and e1.salary=e2.salary and e1.dept_id=e2.dept_id
order by e1.salary

[@pavan-l6e]

SELECT * FROM emp_salary where
concat(dept_id,salary) in
(
select hash from
(
select dept_id,salary,concat(dept_id,salary) as 'hash' from [dbo].[emp_salary]
group by salary,dept_id
having count(*) > 1 )
as ab)

[@monarchadlakha3974]

WITH CTE as (select *, count(salary) over (partition by salary) as CNT from emp_salary
)
Select emp_id, name, salary, dept_id from CTE
where CNT > 1;

[@sandeepagarwal7155]

select a.emp_id
from emp_salary a
join emp_salary b
where a.emp_id != b.emp_id
and a.dept_id = b.dept_id
and a.salary = b.salary

[@naveenbhandari5097]

select * from emp_salary a where salary in (select distinct salary from emp_salary b where a.dept_id = b.dept_id and a.emp_id != b.emp_id)

[@swetha2584]

my solution:
with cte as (
select *, dense_rank() over(partition by dept_id order by salary asc) as rnk
from employees),
cte1 as (
select *, count(rnk) over(partition by dept_id, rnk) as cnt
from cte)
select * from cte1 where cnt > 1;