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:

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_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

[shivamsingla]

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

[mayankbhardwaj]

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_id<>b.emp_id
where a.salary=b.salary
order by a.dept_id

[Reacher]

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_id<>b.emp_id and a.dept_id=b.dept_id and a.rnk=b.rnk;

[kailashpatro]

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

[reachrishav]

Thanks for sharing such good questions. My approach to this was:

SELECT *
FROM emp_salary e1
WHERE EXISTS(
SELECT 1 FROM emp_salary e2 where e1.salary = e2.salary and e1.emp_id <> e2.emp_id
)
ORDER BY dept_id

[amitpatil]

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

[Brown_Munda_oo]

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

[surajn]

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_id<>b.emp_id and a.dept_id=b.dept_id and a.salary=b.salary)
order by a.dept_id

[ls]

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;

[saritha-oj]

Hi Ankit,

This is my Approach


select a.* from emp_salary a
join
emp_salary b
on a.emp_id<b.emp_id or a.emp_id>b.emp_id
where a.salary=b.salary and a.dept_id=b.dept_id
order by a.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;

[swtannie]

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

[venkataram]

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

[swathijaiganesh]

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

[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....

[NikhilGarg-uops]

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

[tamijuddinaymadar]

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_id<>b.emp_id and a.salary=b.salary and a.dept_id=b.dept_id order by dept_id

[sahilgarg]

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)

[AmrutaKhot-nj]

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;