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

Подписаться 119 тыс.

Просмотров 22 тыс.

50% 1

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:
• 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_...
#sql #interview #dataengineer #analytics

Опубликовано:

17 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 168

@prakarshjain570 7 месяцев назад

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)

@TarotWorld-ur7ln 5 месяцев назад

Does ths works ?

@Chathur732 Месяц назад

@@TarotWorld-ur7ln yes it does work and this is the simplest solution. i was surprised why i didnt think of this method earlier. SELECT department_id, AVG(salary) FROM emp_new_1 e1 GROUP BY department_id HAVING AVG(salary) < ( SELECT AVG(salary) FROM emp_new_1 e2 WHERE e2.department_id != e1.department_id ) good work @prakarshjain570

@vinil9212 Месяц назад

you won't be able to agregate the avg salary outside the department from this,only drawback I guess

@Dhanushts-g7x Год назад

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 department_idemp.department_id) av from emp) select distinct department_id department_id from cte2 where dep_avg

@moshemoses6249 Год назад

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

@Tusharchitrakar 10 месяцев назад

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

@kumarashirwadmishra7414 8 месяцев назад

WITH CTE AS( SELECT EMP.DEPT_NAME, ( SELECT AVG(TEMP.SALARY) FROM TEMP.EMPLOYEE WHERE TEMP. DEPT_NAME = EMP. DEPT_NAME) AS DEPT_AVG, ( SELECT AVG(TEMP.SALARY) FROM TEMP.EMPLOYEE WHERE TEMP. DEPT_NAME EMP. DEPT_NAME) AS COMP_AVG FROM EMPLOYEE EMP GROUP BY EMP. DEPT_NAME ) SELECT DEPT_NAME FROM CTE WHERE DEPT_AVG < COMP_AVG; I think this above query also works fine and easy to understand.

@vandanaK-mh9zo 8 месяцев назад

good one!!. Thanks for sharing

@mohdtoufique3786 Год назад

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

@beneficial4u572 Год назад

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

@ankitbansal6 Год назад

Thank you so much 😀

@muditmishra9908 Год назад

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

@ankitbansal6 Год назад

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

@shivayshakti6575 Год назад

Ankit Bhai zindabad ❤

@gauravmaheshwari1807 Год назад

Hello Sir, one more solution: Approach is somewhat similar to yours, but in implementation, I again used one more cte to find out the table totals, and then joined both ctes using cross join. Here this would be efficient to do so because in the first cte, we would only have as many rows as the number of departments, and in the second cte, we will have only one row. I would like to know your inputs on this. Thanks for this amazing question. Also, I was able to come up with solution and write it all down within 10 minutes, so how do you think this is with reference to an interview process? with cte1 as ( select dep_id, avg(salary) avg_dep_salary, sum(salary) total_dep_salary, count(*) total_dep_count from public.ppl_table group by 1 ), cte2 as ( select sum(salary) total_salary, count(*) total_count from public.ppl_table ) select dep_id, avg_dep_salary, (1.0*(total_salary-total_dep_salary))/(total_count-total_dep_count) avg_salary from cte1 cross join cte2 where avg_dep_salary < (1.0*(total_salary-total_dep_salary))/(total_count-total_dep_count);

@MubarakAli-qs9qq 4 дня назад

Done sir

@tanmaymodi8284 10 дней назад

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

@ahmedhusain5415 Год назад

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 - dept_sum)/(total_count-dept_count) as company_avg from cte)A where dept_avg

@vyabinivenkatesan7839 Год назад

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 department_id!=e1.department_id) as avg_sal_other_depts from emp e1 group by e1.department_id) Select * from cte where average_salary

@reshmashaik6606 Год назад

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 a.department_idb.department_id) select distinct department_id from cte2 where dpt_avg

@ShrinchaRani Год назад

Very nice attempt

@lakshaykhanna2462 Год назад

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

@Datapassenger_prashant 2 месяца назад

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

@rohithr9122 3 месяца назад

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)

@rajendramaharjan5018 Год назад

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 ct.dept_avgsal

@AnkitSharma-ln3rw 11 месяцев назад

My solution using Over clausewith t1 as ( select department_id as d, avg(salary) as sal from emp group by department_id) , t2 as ( select t1.d, ((Avg(t1.sal) over(ORDER BY t1.d ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) + (Avg(t1.sal) over(ORDER BY t1.d ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING))/2) as comp_avg from t1) select t2.d from t2 where sal

@anirvansen2941 8 месяцев назад

MYSQL Solution with base as ( select department_id,avg(salary) as avg_salary from emp group by department_id ) ,distinct_department_id as ( select distinct department_id from emp ), dept_logic as ( select t1.department_id, avg(salary) as avg_salary from distinct_department_id t1 inner join emp t2 on t1.department_id t2.department_id group by t1.department_id ) select t1.department_id from base t1 join dept_logic t2 on t1.department_id = t2.department_id and t1.avg_salary < t2.avg_salary

@rajasharma9886 4 месяца назад

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 a.department_idb.department_id group by b.department_id )

@RiyaAggarwal-f2e Год назад

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) ;

@007SAMRATROY Год назад

with cte_a as ( select a.department_id as a_dept, a.salary as a_sal, b.department_id as b_dept, b.salary as b_sal from emp a, emp b where a.department_id b.department_id ), cte_b as ( select a_dept, avg(a_sal) as dept_avg_sal, avg(b_sal) as company_avg_sal from cte_a group by a_dept ) select * from cte_b where dept_avg_sal < company_avg_sal; This one should provide the exact answer to the question i.e., the department which has lower average salary than company average as well the concerned department average salary and company average salary(excluding the department).

@anirbanbiswas7624 13 дней назад

also can be DONE BY LEFT JOIN WHERE I SHOULD JOIN ON THE CONDITION e1.department_ide2.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 e1.department_ide2.department_id group by e1.department_id having total_avg

@himanshuparihar9888 Год назад

select distinct department_id from (select department_id , round(avg(salary) over(partition by department_id ) ) as da , round(avg(salary) over()) as al from emp ) as k where da

@GowthamR-ro2pt 4 месяца назад

Hi folks 😁 this works : with cte as (SELECT department_id,avg(salary)Avg,sum(salary)Sal,count(emp_id)Cnt from emp1 group by department_id),cte1 as (select e.department_id,e.Avg,sum(e1.Sal) over (partition by e.department_id)/sum(e1.Cnt) over (partition by e.department_id)Tot_avg from cte e join cte e1 on e.department_ide1.department_id) select * from cte1 where Avg

@vikasvk9174 Год назад

I am able to solve it my selft but it took 15-20 min They you explain you question its automaticall solve half problem Thank you.

@ankitbansal6 Год назад

Awesome

@nallurusaikiran678 8 месяцев назад

Hi Ankit,Here is my solution with cte as ( select department_id,avg(salary) as dept_avg from emp group by department_id ) select *, company_avg = (select avg(salary) from emp where emp.department_id cte.department_id) from cte where dept_avg < (select avg(salary) from emp where emp.department_id cte.department_id)

@AshutoshVerma-d4z Год назад

EASY and CLEAN solution without self-join- select distinct department_id, dept_salary from ( select *, count(*) over() as total_count, count(*) over(partition by department_id) as dept_count, sum(salary) over() as tot_salary, sum(salary) over(partition by department_id) as dept_salary, avg(salary) over(partition by department_id) as dept_avg_salary from emp) as t where dept_avg_salary

@Shubhamsharma-il7fy 10 месяцев назад

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 e.department_id!=e2.department_id then e2.salary else 0 end) from emp e join emp e2 on e.department_id!=e2.department_id group by e.department_id having avg(e.salary)

@vibhavbhat2011 2 месяца назад

My Solution using sub query with cte as (select department_id , round(avg(salary) ,0)as average_salary from emp group by department_id ) select * from cte as c where average_salary < (select avg(salary) from emp where department_id c.department_id)

@shubhankargupta4385 Год назад

The way it is explained , it's looks so easy.

@razimoosa7028 Год назад

Hi Ankit, select department_id,avg(salary) as dept_sal from emp e group by department_id having avg(salary) < (select avg(salary) from emp f where f.department_id not in (e.department_id)) Kindly let me know if we can use this approach. Thank you so much!

@artsbypratik Год назад

I guess this works, simple and easy 👍

@ankitbansal6 Год назад

Yes this will give the result but for large datasets it might give performance issues as for each row of outer table the inner query will execute.

@sankuM Год назад

Did you try it in any SQL based editor???

@sankuM Год назад

@ankitbansal6 👆

@reachrishav Год назад

Nice approach. I guess this will be a bit more performant. with dept_cte as( select department_id, avg(salary) as avg_sal from emp group by department_id ) select department_id,avg_sal as dept_sal from dept_cte d where avg_sal < (select avg(salary) from emp e where e.department_id d.department_id)

@rohitbande5758 Год назад

select distinct e1.department_id from emp e1 group by e1.department_id having avg(salary)

@AravindKumar-es4nd 5 месяцев назад

;with cte as( select emp_id,emp_name,department_id,salary, (select avg(salary) from emp d where d.department_id e.department_id) rem_dep_avg_sal from emp e) ,cte2 as (select c.*,s.avg_dep_sal from cte c join (select department_id,avg(salary) avg_dep_sal from emp group by department_id) s on c.department_id = s.department_id) select distinct department_id from cte2 where rem_dep_avg_sal > avg_dep_sal

@rubyshorts281 Месяц назад

easy to understand mysql solution with cte_tot as ( select sum(salary) tot_sal, count(emp_id) tot_emp from emp ), cte_dept as( select dept_id , sum(salary) as dept_sal, count(emp_id) as dept_emp from emp group by dept_id ), cte_comp as ( select dept_id , ((select tot_sal from cte_tot) - dept_sal) as other_dept_sal, ((select tot_emp from cte_tot)- dept_emp) as other_dept_count, dept_sal, dept_emp from cte_dept ) select dept_id, case when ((dept_sal*1.0)/dept_emp) > ((other_dept_sal*1.0)/other_dept_count) then 1 else 0 end as greater_than_cmpny_avg_flg from cte_comp

@Narendra_live 8 месяцев назад

with cte as( select department_id,avg(salary) as dep_avg_sal from emp group by department_id ), cte2 as( select emp.*,cte.department_id as excluded_dept from emp emp inner join cte cte on(emp.department_id cte.department_id) ), cte3 as ( select excluded_dept,avg(salary) as compare_salary from cte2 group by excluded_dept ) select *from cte cte inner join cte3 cte3 on(cte.department_id=cte3.excluded_dept) where cte.dep_avg_sal

@LS8636 Год назад

Thanks Ankit for this..Here is my approach :- SELECT d.department_id,avg(k.salary) as comp_avg ,avg(d.salary) as dept_avg from emp d inner join emp K on d.department_id != k.department_id GROUP by d.department_id having avg(d.salary) < avg(k.salary) Does it have any performance issue with large databases? Please reply

@anshulsrivastava2404 Год назад

This looks good since we are calculating average here so even duplication of records will be taken care. I find this as a straight forward solution and correct solution @lalitsinghjeena8636

@nangaveramari7563 2 дня назад

select e.department_id from emp e group by department_id having avg(salary)

@vikaskumar-qr5tj Год назад

Nice question again and power of self join was just wow....

@ankitbansal6 Год назад

Glad you liked it

@abhishekpurohit3442 2 месяца назад

easier solution using self join: with base as ( select a.department_id, avg(a.salary) as avg_salary from emp a group by 1 ) select a.*, avg(b.salary) as company_avg_salary from base a left join emp b on a.department_id!= b.department_id group by 1,2

@rhugvedsatardekar9229 Месяц назад

select e1.department_id, avg(e1.salary) Avg_sal from emp e1 group by e1.department_id having avg(salary) < (select avg(salary) from emp e2 where e2.department_id e1.department_id)

@MixedUploader 8 месяцев назад

Ankit superb solution for superb question. Thank you so much.

@ankitbansal6 8 месяцев назад

Keep watching

@mathman298 Месяц назад

select department_id , avg(salary) department_avg_salary--,count(distinct emp_id) department_avg_salary ,( (select distinct sum(salary) from emp) - sum(salary) ) / ((select count(distinct emp_id) from emp) - count(distinct emp_id)) as oth_department_combo_avg from emp group by department_id having avg(salary)

@stat_life Год назад

Thank you so much sir for always bringing these wonderful questions I will definitely try this now

@ankitbansal6 Год назад

All the best 😊

@Advanced_Learner Год назад

with cte as ( select * , sum(salary) over (partition by dept_name)/count(emp_name) over (partition by dept_name) as average_salary_dept , (sum(salary) over () - sum(salary) over (partition by dept_name))/ (count(emp_name) over () - count(emp_name) over (partition by dept_name)) as average_Salary_Minus_Dept from dept_Salary ) select dept_name , average_salary_dept , average_Salary_Minus_Dept from cte where average_salary_dept < average_Salary_Minus_Dept group by dept_name

@arupchandra8602 8 месяцев назад

with cte as( select department_id ,AVG(salary) sal from emp group by department_id) select * from cte e1 where e1.sal

@thakurneerajsingh4234 8 месяцев назад

with cte as( select department_id,AVG(salary) as avg_sal,sum(salary) as total_sal,count(*) total_emp from emp group by department_id) ,ctf as( select a.department_id,a.avg_sal,sum(b.total_sal)/sum(b.total_emp) as avg_other_dept from cte a join cte b on a.department_id != b.department_id group by a.department_id,a.avg_sal) select * from ctf where avg_sal < avg_other_dept

@reshmaammu4726 Год назад

with cte AS (select department_id, avg(salary) as department from emp group by department_id), cte2 as (select distinct a.department_id, a.department, avg(b.salary) as total from cte a, emp b where a.department_idb.department_id group by a.department_id, a.department) select department_id from cte2 where department

@da8233 Год назад

in this video , i understood importance of self joins

@ujjwalvarshney3188 Год назад

create temp table uj as ( select department_id , avg(salary) as averag_sal from emp group by 1); select a.department_id from uj a join uj b on a.department_id b.department_id group by 1 having avg(a.averag_sal) < avg(b.averag_sal) order by 1

@AmanVerma-cu3lp 8 месяцев назад

If we only need list of departments then this query should work: select e1.department_id from emp e1 group by e1.department_id having avg(e1.salary) < (select avg(e2.salary) from emp e2 where e1.department_id != e2.department_id)

@enisertem9738 11 месяцев назад

with base as ( select department_id, avg(salary) dep_avg, count(1) no_of_emp, sum(salary) dep_salary from emp group by department_id) , c1 as ( select b1.department_id,b1.dep_avg,(sum(b2.dep_salary) *1.0 / sum(b2.no_of_emp)) other_avg from base b1 join base b2 on b1.department_id!=b2.department_id group by 1,2) select department_id from c1 where dep_avg

@MuskanGoyal-db7cs 3 месяца назад

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

@sandeepanand3834 12 дней назад

with cte as ( select department_id, avg(salary) avg_sal_dept from emp group by department_id ), cte2 as ( select a.department_id, a.avg_sal_dept, avg(b.avg_sal_dept) comp_avg_sal from cte a join cte b on a.department_id != b.department_id group by a.department_id ) select department_id from cte2 where avg_sal_dept < comp_avg_sal

@nidhisinghai0309 Месяц назад

with dep_avg as (select department_id,avg(salary) avg_salary from emp group by department_id) select d.department_id,d.avg_salary,avg(salary) from dep_avg d join emp on d.department_idemp.department_id group by d.department_id,d.avg_salary

@sahilummat8555 3 месяца назад

Absolutely amazing

@balipavankalyan5008 Месяц назад

Great brother

@chetanphalak7192 10 месяцев назад

Amazing solution Ankit

@gopinathg318 2 месяца назад

with dept_avg as( select department_id,avg(salary) as dept_avg from emp group by department_id ) ,org_avg as( select e1.department_id, avg(e2.salary) as org_avg from emp e1 inner join emp e2 on e1.department_ide2.department_id group by e1.department_id ) select d.department_id ,dept_avg,org_avg from dept_avg d inner join org_avg o on d.department_id=o.department_id where dept_avg

@NilayMukhopadhyay Год назад

You are a true GURU. 🙏

@vidyasagar-fx5np Год назад

Hi Ankit Can you take a look on my approach with cte as ( select e1.department_id,avg(e1.salary) as dept_avg, (select avg(salary) from emp e where e1.department_id !=e.department_id ) as company_avg from emp e1 group by department_id ) select * from cte where dept_avg < company_avg ; ;

@saikatde6343 Год назад

Hi Ankit Sir: Can you please see if this approach works: select a.department_id , departments_avg_sal from ( select a.department_id , avg(b.salary) as total_sal , avg(a.salary) as departments_avg_sal from emp as a inner join emp as b on a.department_id != b.department_id group by a.department_id ) as a where departments_avg_sal < total_sal ;

@gauravmaheshwari1807 Год назад

Great solution. I think this should work.

@gobindaroy7811 Год назад

Thanks Sir. The explanation was lucid

@artsbypratik Год назад

Hi Ankit, WITH cte AS (select sum(salary) as sum_sal, count(salary) as total_count from emp) select department_id from (select department_id, avg(salary) as dep_avg, ((select sum_sal from cte) - sum(salary))/((select total_count from cte)-count(salary)) as comp_avg from emp group by 1) where comp_avg>dep_avg Will this be more performant as we are not using self join? I have used the formula to calculate the avg of the remaining department's using (sum of all salary - the sum of respective department salary)/(count of the remaining department's emp). Eg - Considering for dep 100, the other department (200,300) avg will be (91000-40000)/6 = 8500 I have validated the query. Thanks!

@jacobreddydevidi1305 Год назад

this query is giving only dep id

@jacobreddydevidi1305 Год назад

executable query is as follows : WITH cte AS (select sum(salary) as sum_sal, count(salary) as total_count from [dbo].[paypal_avg_qsn_emp]) select department_id from (select department_id, avg(salary) as dep_avg, ((select sum_sal from cte) - sum(salary))/((select total_count from cte)-count(salary)) as comp_avg from [dbo].[paypal_avg_qsn_emp] group by department_id) a where comp_avg >dep_avg

@grzegorzko55 Год назад

Your videos are avensome ❤

@Convivialranjan Год назад

Awesome ❤ you're master of sql

@KisaanTuber Год назад

Hi Ankit. Here is my solution: with t1 as (SELECT e1.department_id , avg(salary) as comp_avg_sal from (select DISTINCT department_id from emp) e1 LEFT join emp e2 on e1.department_id e2.department_id GROUP by 1), t2 as (SELECT department_id , avg(salary) as dept_avg_sal from emp GROUP by 1) SELECT t1.department_id from t1 LEFT join t2 on t1.department_id = t2.department_id WHERE dept_avg_sal < comp_avg_sal;

@jacobreddydevidi1305 Год назад

complicated query giving only department_id , from performance wise using join over correlated query is a good choice though , making it more dynamic will be great

@Esinha12 Год назад

I could solve, I think I can work for PayPal now Edit: Wow a heart from creator PS: I am already working with PayPal 😃😅

@ankitbansal6 Год назад

😁

@jaymistry2876 Год назад

Awesomeee guruyy🎉🎉🎉🎉🎉🎉

@avi8016 Год назад

Great explanation sir!! Loved the approach💯

@ankitbansal6 Год назад

Glad you liked it

@deepanrajusugavasan9762 Год назад

i have solved it by using logic behind calculating average. is it okie ? WITH SS AS ( SELECT *, SUM(salary) OVER(PARTITION BY department_id) AS dept_sum, SUM(salary) OVER() AS sum_sal, COUNT(*) OVER() as TOTAL, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg, COUNT(emp_id) OVER(PARTITION BY department_id) AS dept_count FROM Emp ) SELECT department_id FROM SS WHERE ((sum_sal - dept_sum) / TOTAL - dept_count) > dept_avg GROUP BY department_id ;

@ankitbansal6 Год назад

Good one 👍

@rakeshranjan7647 Месяц назад

My solution with cte as ( select department_id,AVG(salary) as Avgsal, (select Company_sal=AVG(salary) from emp e where e.department_ide1.department_id ) Company_sal from emp e1 group by department_id) select * from cte where avgsal

@sahilgarg7226 Год назад

Hi Ankit, This is the solution I thought. Please let me know if I have to take care of anything here. with cte as ( select * ,AVG(salary) OVER (PARTITION BY department_id) as Avg_Department_Wise ,AVG(salary) Over () as Total_Avg ,SUM(salary) OVER (PARTITION BY department_id) as Sum_Department_Wise ,SUM(salary) Over () as Total_Sum --,COUNT(salary) ,Count(1) OVER () AS Total_Count ,COUNT(1) OVER (PARTITION BY DEPARTMENT_ID) as Count_Department_Wise from emp1 ),cte2 as ( select distinct department_id,Avg_Department_Wise,(Total_sum - Sum_Department_Wise)/(Total_Count - Count_Department_Wise) as Result_Needed from cte ) select department_id from cte2 where Result_Needed > Avg_Department_Wise

@yatharthchauhan7407 11 месяцев назад

Amazing solution. Its really helpful. ❤

@ankitbansal6 11 месяцев назад

Glad to hear that

@Advanced_Learner Год назад

with cte as ( Select department_name , avg(salary) as dept_Avg_salary from dept a group by department_name having dept_Avg_salary < (Select avg(salary) from dept b where b.department_name a.department_name))

@jjayeshpawar 6 месяцев назад

with cte as ( select *, sum(salary) over(partition by department_id) as dept_sum, sum(salary) over() as total_sum, count(emp_id) over(partition by department_id) as dep_cnt, count(emp_id) over() as total_cnt from emp) SELECT DISTINCT department_id from ( SELECT *,dept_sum/dep_cnt as dept_avg, (total_sum-dept_sum)/(total_cnt-dep_cnt) as total_avg from cte) a where dept_avg

@praneethmuragani Год назад

select department_id from (select department_id from emp group by department_id) t1 where (select avg(salary) from emp where department_id = t1.department_id) < (select avg(salary) from emp where department_id t1.department_id)

@KrishnaPrashanthGajulapalle 10 месяцев назад

I feel this would be much easier... Can you please check and comment on this solution? with dept as ( select distinct department_id as depid from emp ) select depid from dept,emp group by depid having avg(case when depid = department_id then salary else null end) < avg(case when depid != department_id then salary else null end)

@prajjwaljaiswal3419 9 месяцев назад

I think I am missing something . Please check my answer. select a.department_id, avg(a.salary) as avg_salary, avg(b.salary) as comp_avg_sal from emp a inner join emp b on a.department_id != b.department_id group by a.department_id having avg(a.salary) < avg(b.salary) order by department_id ;

@dwaipayansaha4443 2 месяца назад

My SOlution: with t1 as( select e.department_id, avg(ee.salary) avg_comp_sal from emp e left join emp ee on e.department_idee.department_id group by e.department_id order by 1) select e.department_id, avg(salary) avg, t1.avg_comp_sal from emp e inner join t1 on e.department_id=t1.department_id group by 1 having avg

@chinmayabastia3308 8 месяцев назад

thanks for video, can it be done by corelated query......select department_id,AVG(salary) as avg_salary from emp a group by department_id having AVG(salary) > (select AVG(salary) from emp where department_id a.department_id )

@kushalmk9464 Год назад

select e1.department_id, avg(e1.salary) from epaypal e1 group by e1.department_id having avg(e1.salary) < (select avg(e2.salary) from epaypal e2 where e1.department_id e2.department_id) HI Ankit can we use this approach.Thank you

@ankitbansal6 Год назад

Yes this will give the result but for large datasets it might give performance issues as for each row of outer table the inner query will execute.

@kushalmk9464 Год назад

@@ankitbansal6 Thanks for the clarification

@pinaakgoel2937 7 месяцев назад

select department_id from ( select department_id, avg(salary) avg_dept_sal from emp group by department_id) t1 where avg_dept_sal

@swarajpatil6447 Год назад

Thanks for the content!

@rahuljain4107 9 месяцев назад

Amazing!

@BigDataWithSky Год назад

pls always mention the question as well

@sambasiva_368 Год назад

Hi Ankit, I see, most of the times you use cte only rather derived table. Can you please tell me y?( Wanted to know will there be any performance improvement?)

@ankitbansal6 Год назад

For complex cte and good amount of data derived tables make sense

@prashantmhatre9225 Год назад

Man gaye bhai 👍👍🙏

@shinejohnson777 6 месяцев назад

select l.* from( select department_id,avg(salary) as dept_sal ,round((select avg(salary) from empll h where h.department_idm.department_id),0) as gg from empll m group by department_id)l where dept_sal

@udhhavarora5483 Год назад

with t1 as (select department_id, avg(salary) as comp_avg from ( with cte as(select a.department_id,b.department_id as other_id,b.salary from emp a cross join emp b on a.department_id b.department_id ) select * from cte ) group by department_id order by department_id), t2 as (select department_id,avg(salary) as dept_avg from emp group by department_id) select a.department_id from t1 a inner join t2 b on a.department_id = b.department_id and dept_avg

@ANKITSINGH-fy9ub Год назад

Can we use below approach:- Get a cte from group by on dept id, then for calculating the dynamic avg we can put that in a sub query with where clause that subquery filtering all dept which are not same as current one

@ankitbansal6 Год назад

Try it out

@saikatchakraborty5536 Год назад

Awesome!

@ankitbansal6 Год назад

Thank you! Cheers!

@shishirkumar4124 Год назад

Hi @Ankit, will it work _______________________________________________________________ select distinct t1.*, (select avg(avgSalary) from tempT t2 where t1.department_id != t2.department_id) from tempT t1 where t1.avgSalary < (select avg(avgSalary) from tempT t2 where t1.department_id != t2.department_id)

@anish_bhateja Год назад

with cte as (select department_id as dept_id,avg(salary) as avg_sal from paypal_emp group by 1), cte2 as (select a.dept_id,a.avg_sal as dept_sal,b.avg_sal as comp_sal from cte a, cte b where a.dept_id != b.dept_id order by 1) select dept_id,round(avg(dept_sal),0) as avg_dept_sal,round(avg(comp_sal),0) as avg_comp_sal from cte2 group by 1 having avg(dept_sal) < avg(comp_sal);

@kirangadhe4962 Год назад

slightly different approach with cte as ( SELECT department_id, avg(salary) dept_avg from emp group by department_id ) , cte2 as ( SELECT A.department_id avg_dept , B.department_id as join_dept from ( select department_id from emp group by department_id )A cross join ( select department_id from emp group by department_id )B where A.department_id B.department_id ), cte3 as ( SELECT cte2.avg_dept as dept ,avg(salary) as cmp_avg from emp inner join cte2 on cte2.join_dept = emp.department_id group by cte2.avg_dept ) SELECt cte.*,cte3.cmp_avg from cte inner join cte3 on (cte.department_id = cte3.dept) where cte.dept_avg

@nivadonga6980 Год назад

hi Ankit, Can we use this? Or is this too much performance inefficient? with cte as (select department_id,avg(salary)::int as avg_salary, count(*) as no_of_emp, sum(salary)::integer as total_salary from emp group by department_id order by department_id), cte2 as (select *, (COALESCE(SUM(total_salary) OVER (ORDER BY total_salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)::int,0) + COALESCE(SUM(total_salary) OVER (ORDER BY total_salary ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)::int,0)) as company_total, (COALESCE(SUM(no_of_emp) OVER (ORDER BY no_of_emp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)::int,0) + COALESCE(SUM(no_of_emp) OVER (ORDER BY no_of_emp ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)::int,0)) as company_emp from cte order by department_id) select department_id from cte2 where avg_salary

@akulghulyani4754 Год назад

with t as ( with p as ( with x as ( select department_id,salary, avg(salary) over (partition by department_id) as sal_dept from emp) select a.department_id as main_dept,a.salary,b.department_id,a.sal_dept, b.salary as se from x as a join x as b on a.department_idb.department_id order by a.department_id,a.salary,b.department_id) select main_dept as dept_id,sal_dept as avg_sal_dept, avg(se) over (partition by main_dept) as dyn_sal from p) select distinct dept_id,avg_sal_dept,dyn_sal from t where avg_sal_dept

@janakmali8243 Год назад

Sql MASTER. 🙏🙏🙏🙏🙏

@subhojitchatterjee6312 Год назад

My solution using correlated sub query: WITH CTE AS (SELECT*,AVG(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS DEPT_SALARY FROM EMP_P) SELECT DISTINCT DEPARTMENT_ID FROM CTE AS C1 WHERE DEPT_SALARY