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

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

USE SSIS

--DROP TABLE PayPal_emp--------------

#𝗜𝗻𝘁𝗿𝗲𝘃𝗶𝗲𝘄𝗣𝗿𝗲𝗽𝗮𝗿𝗮𝘁𝗶𝗼𝗻𝟭𝟬𝟬𝗱𝗮𝘆𝘀
𝗧𝗼𝗽𝗶𝗰-𝗦𝗤𝗟
𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻-2
------------🅿🅰🆈🅿🅰🅻 🅸🅽🆃🅴🆁🆅🅸🅴🆆 🆀🆄🅴🆂🆃🅸🅾🅽 -----------------------

 𝙁𝙞𝙣𝙙 𝙩𝙝𝙚 𝘿𝙚𝙥𝙖𝙧𝙩𝙢𝙚𝙣𝙩 𝙬𝙝𝙤𝙨𝙚 𝙨𝙖𝙡𝙖𝙧𝙞𝙚𝙨 𝙖𝙧𝙚 𝙡𝙚𝙨𝙨 𝙩𝙝𝙖𝙣 𝙘𝙤𝙢𝙥𝙖𝙣𝙞𝙚𝙨 𝙨𝙖𝙡𝙖𝙧𝙞𝙚𝙨 𝙗𝙪𝙩 𝙩𝙝𝙚 𝙙𝙪𝙧𝙞𝙣𝙜 𝙩𝙝𝙚 𝙘𝙤𝙢𝙥𝙖𝙧𝙞𝙣𝙜 𝙩𝙝𝙚 𝙃𝙍 𝘿𝙚𝙥𝙖𝙧𝙩𝙢𝙚𝙣𝙩'𝙨 𝘼𝙫𝙚𝙧𝙖𝙜𝙚 𝙨𝙖𝙡𝙖𝙧𝙮 𝙬𝙞𝙩𝙝 

𝘾𝙤𝙢𝙥𝙖𝙣𝙮'𝙨 𝙖𝙫𝙚𝙧𝙖𝙜𝙚 𝙨𝙖𝙡𝙖𝙧𝙮 (𝙃𝙍 𝘿𝙚𝙥𝙖𝙧𝙩𝙢𝙚𝙣𝙩 𝙩𝙤 𝙗𝙚 𝙚𝙭𝙘𝙡𝙪𝙙𝙚𝙙 𝙙𝙪𝙧𝙞𝙣𝙜 𝙩𝙝𝙚 𝙘𝙖𝙡𝙘𝙪𝙡𝙖𝙩𝙞𝙤𝙣 𝙩𝙝𝙚 𝙘𝙤𝙢𝙥𝙖𝙣𝙮'𝙨 𝙖𝙫𝙚𝙧𝙖𝙜𝙚 𝙨𝙖𝙡𝙖𝙧𝙮) 𝙖𝙣𝙙 same condition to be applied for 𝙤𝙩𝙝𝙚𝙧 𝙙𝙚𝙥𝙖𝙧𝙩𝙢𝙚𝙣𝙩

--------------------------------------------Sample Table Created ------------------------------------------------------------

create table PayPal_emp(

emp_id int,

emp_name varchar(20),

department_id int,

salary int,

manager_id int,

emp_age int);

insert into PayPal_emp

values

(1, 'Abinash', 100,10000, 4, 39);

insert into PayPal_emp

values (2, 'Mohit', 100, 15000, 5, 48);

insert into PayPal_emp

values (3, 'Vikas', 100, 10000,4,37);

insert into PayPal_emp

values (4, 'Rohit', 100, 5000, 2, 16);

insert into PayPal_emp

values (5, 'Mudit', 200, 12000, 6,55);

insert into PayPal_emp

values (6, 'Agam', 200, 12000,2, 14);

insert into PayPal_emp

values (7, 'Sanjay', 200, 9000, 2,13);

insert into PayPal_emp

values (8, 'Ashish', 200,5000,2,12);

insert into PayPal_emp

values (9, 'Mukesh',300,6000,6,51);

insert into PayPal_emp

values (10, 'Rakesh',300,7000,6,50);

SELECT * FROM PayPal_emp

-----------------------------------------------------Solution-----------------------------------------------------------------

WITH CTE_PayPal  as (

SELECT department_id, SUM(salary) 'Total_Salary', COUNT(*) as 'No_of_Employee' , AVG(salary) as 'Dept_Average_Salary'    

FROM PayPal_emp

GROUP BY department_id

),CTE_PayPal_Desired_Salary as (

SELECT p1.department_id,p1.Dept_Average_Salary, SUM(p2.No_of_Employee) as 'No of Employee',sum(p2.Total_Salary) as 'Total Salary',

 + (SUM(p2.Total_Salary)/SUM(p2.No_of_Employee)) as 'Company_Avg_Salary'

FROM CTE_PayPal p1

INNER JOIN CTE_PayPal p2 ON p1.department_id != p2.department_id

GROUP BY p1.department_id,p1.Dept_Average_Salary

)

SELECT * FROM CTE_PayPal_Desired_Salary WHERE Company_Avg_Salary >Dept_Average_Salary

----------------------------End of the Solution---------------------