PayPal SQL Interview Problem (Level Hard) | Advanced SQL Problem

In this video we will solve a PayPal SQL Interview problem. This is very advanced SQL problem and requires good hold on writing SQL queries.

Here is the ready script:
create table emp(
emp_id int,
emp_name varchar(20),
department_id int,
salary int,
manager_id int,
emp_age int);

insert into emp
values
(1, 'Ankit', 100,10000, 4, 39);
insert into emp
values (2, 'Mohit', 100, 15000, 5, 48);
insert into emp
values (3, 'Vikas', 100, 10000,4,37);
insert into emp
values (4, 'Rohit', 100, 5000, 2, 16);
insert into emp
values (5, 'Mudit', 200, 12000, 6,55);
insert into emp
values (6, 'Agam', 200, 12000,2, 14);
insert into emp
values (7, 'Sanjay', 200, 9000, 2,13);
insert into emp
values (8, 'Ashish', 200,5000,2,12);
insert into emp
values (9, 'Mukesh',300,6000,6,51);
insert into emp
values (10, 'Rakesh',300,7000,6,50);

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:

#sql #interview #dataengineer #analytics

[prakarshjain]

I just had this very small solution - select department_id, avg(salary) from emp e1 group by department_id having avg(salary) < (select avg(salary) from emp e2 where e2.department_id != e1.department_id)

[Dhanushts-gx]

with cte1 as (select * from emp)
, cte2 as
(select department_id, avg(salary) over(partition by department_id) dep_avg,
(select avg(salary) from cte1 where av
from emp)
select distinct department_id department_id from cte2 where dep_avg<av
(i solved it very easily using corealated query!)

[moshemoses]

What a great question.
Needed your guidance but able to do about 60% of it myself.

[beneficialu]

what an explaination👍👍👍👍👍
No one can so cleanly step by step such an complex query. Really Loved it. This kind of explaination can boost you jump into IT sector.👏👏

[mohdtoufique]

Hi Ankit...Thanks for the content ..My approach
WITH CTE as(
SELECT *, avg(salary)OVER(PARTITION BY department_id)AS avg_dept_salary,
count(emp_id)OVER(PARTITION BY department_id) AS emp_dept_count,
avg(salary)OVER() AS total_avg_salary,
count(emp_id)OVER() AS emp_overall_count
FROM emp),
cte_2 AS(
SELECT *, ((total_avg_salary*emp_overall_count)-(emp_dept_count*avg_dept_salary))/(emp_overall_count-emp_dept_count) AS avg_desired
FROM CTE)
SELECT * FROM cte_2
WHERE avg_dept_salary<avg_desired

[tanmaymodi]

Itried this one:-

with cte as(
select distinct a.department_id, x = (select avg(salary) from emp where department_id <> a.department_id) from emp a
),
cte1 as(
select department_id, y = avg(salary)
from emp
group by department_id
)
select a.department_id from cte a
inner join cte1 b on a.department_id = b.department_id
where x < y

[Tusharchitrakar]

This was my approach and it seems more efficient with only a single line query (although uses subqueries internally but the SQL engine might only calculate it once since its a common value for all iterations) but i might be wrong:

select department_id, avg(salary) as dept_avg,
((select sum(salary) from emp)-sum(salary)) div
((select count(1) from emp)-count(1)) as remaining_avg
from emp group by department_id having dept_avg<=remaining_avg;

This got the job done

[vyabinivenkatesan]

Below is my solution:

With cte as(
Select e1.department_id, AVG(e1.salary) as average_salary, (select AVG(e.salary) from emp e where as avg_sal_other_depts
from emp e1
group by e1.department_id)
Select * from cte where


Please let me know if this is a correct approach.

[muditmishra]

Hi Ankit, thanks for this video. I am sharing my solution below.

select

distinct department_id
from
(
select
emp_1.department_id as department_id,
avg(emp_1.salary) over(partition by emp_1.department_id) as department_avg_salary,
avg(emp_2.salary) over(partition by emp_1.department_id) as avg_salary
from emp emp_1 cross join emp emp_2
where emp_1.department_id != emp_2.department_id
)a
where department_avg_salary < avg_salary

[ahmedhusain]

Solution without the use of a self-join:

with cte as
(
Select department_id, SUM(salary) as dept_sum, AVG(salary) as dept_avg, count(department_id) as dept_count, (Select SUM(salary) from employees) as total_sum,
(select COUNT(department_id) from employees) as total_count
from employees
group by department_id
)
Select * from
(Select department_id, dept_avg, (total_sum - as company_avg
from cte)A
where dept_avg<company_avg

[ankitbansal]

Please give a thumbs up 👍 to the video if you like the question.

[Datapassenger_prashant]

it took me time, which must be way beyond the given time in interview..
but after trying with some complex method and failure, switched to basics and got my answer..

here is my solution:


with cte2 as (
Select department_id, count(*)as cnt, sum(salary) as salary_sum, AVG(salary) as avg_salary_dep
from paypal_employees
group by
department_id
), cte3 as (
Select c1.department_id as c1_dept,
c1.avg_salary_dep as c1_avg_salary
, sum(c2.salary_sum) over ( partition by c1.department_id) /
sum(c2.cnt) over ( partition by c1.department_id) as overall_company_average
from cte2 c1
inner join cte2 c2
on c1.department_id > c2.department_id
or c2.department_id > c1.department_id
)
Select distinct c1_dept
from cte3
where c1_avg_salary < overall_company_average

[anirbanbiswas]

also can be DONE BY LEFT JOIN WHERE I SHOULD JOIN ON THE CONDITION e1.department_id<>e2.department_id, THAT'S IT

select e1.department_id, avg(e1.salary)as total_avg
, avg(e2.salary)as except_avg_sal from emp as e1 left join emp as e2
on
group by e1.department_id having total_avg<except_avg_sal

[lakshaykhanna]

MySQL solution:

SELECT department_id, AVG(Salary) AS dept_avg,
(SELECT AVG(SALARY) FROM emp WHERE department_id <> e1.department_id) AS company_average
FROM emp e1
GROUP BY 1
HAVING dept_avg < company_average

[rajendramaharjan]

Hi Ankit, Below is my approach


with cte as
(
select
department_id,
avg(salary*1.0) as dept_avgsal
from
#emp
group by department_id
)
select
ct.department_id,
ct.dept_avgsal,
avg(c.salary*1.0) as overallsal
from
cte ct join #emp c
on ct.department_id !=c.department_id
group by
ct.department_id,
ct.dept_avgsal
having

[RiyaAggarwal-fe]

Hi, here's my piece of query:

select e.department_id, avg(e.salary) from emp e group by e.department_id
having avg(e.salary) < (select avg(m.salary) from emp m where m.department_id != e.department_id) ;

[rohithr]

select distinct department_id from(
select department_id, AVG(salary)over(partition by department_id order by department_id)avg_sal_dept
from emp) t1
where t1.avg_sal_dept < (
select AVG(salary) from emp as e1
where e1.department_id <> t1.department_id)

[reshmashaik]

with cte as
(select distinct department_id, avg(salary) as dpt_avg from emp
group by department_id),
cte2 as
(select distinct a.department_id, a.dpt_avg, avg(b.salary) over(partition by a.department_id) as average
from cte a, emp b
where
select distinct department_id from cte2
where dpt_avg<average

[Shubhamsharma-ilfy]

Sir i have done this question this way please have a look:
select e.department_id, avg(e.salary) as avg_sal, avg(case when then e2.salary else 0 end)
from emp e join emp e2 on
group by e.department_id
having avg(e.salary)<avg(case when then e2.salary else 0 end)

[rajasharma]

with cte as (
select emp_id, emp_name, department_id, salary from emps
),
(
select b.department_id, avg(b.salary) as avg_emp_sal,
avg (case when a.department_id != b.department_id then a.salary end)as avg_company_sal
from cte a join cte b on

group by b.department_id
)