2 LINKEDIN POWER CREATOR SQL PROBLEM
--The LinkedIn Creator team is looking for power creators who use their personal profile as a company
--or influencer page.
--This means that if someone's Linkedin page has more followers than all the company they work for,
--we can safely assume that person is a Power Creator.
--Write a query to return the IDs of these LinkedIn power creators in ascending order.
--Assumptions:
--A person can work at multiple companies.
--In the case of multiple companies, WE NEED TO DO SUM OF ALL THE COMPANY'S FOLLOWERS.
CREATE TABLE personal_profiles
(
profile_id INT,
name VARCHAR(100),
followers INT
)
--INSERT INTO personal_profiles VALUES(1,'Harshit Bhadiyadra',92000)
--INSERT INTO personal_profiles VALUES(2,'Neha Jain',199000)
--INSERT INTO personal_profiles VALUES(4,'Janvi Soni',107000)
--INSERT INTO personal_profiles VALUES(5,'Ambika Soni',139000)
--INSERT INTO personal_profiles VALUES(6,'Deeksha',39000)
--INSERT INTO personal_profiles VALUES(7,'Pooja',91000)
--INSERT INTO personal_profiles VALUES(8,'Bhadiyadra',43000)
--INSERT INTO personal_profiles VALUES(3,'Hema',171000)
CREATE TABLE employee_company
(
personal_profile_id INT,
company_id INT
)
--INSERT INTO EMPLOYEE_COMPANY VALUES(1,9)
--INSERT INTO EMPLOYEE_COMPANY VALUES(2,2)
--INSERT INTO EMPLOYEE_COMPANY VALUES(3,1)
--INSERT INTO EMPLOYEE_COMPANY VALUES(3,10)
--INSERT INTO EMPLOYEE_COMPANY VALUES(4,3)
--INSERT INTO EMPLOYEE_COMPANY VALUES(5,6)
--INSERT INTO EMPLOYEE_COMPANY VALUES(6,5)
--INSERT INTO EMPLOYEE_COMPANY VALUES(7,7)
--INSERT INTO EMPLOYEE_COMPANY VALUES(8,8)
CREATE TABLE company_pages
(
company_id INT,
name VARCHAR(100),
followers INT
)
--INSERT INTO company_pages VALUES(1,'The Data Science Podcast',8000)
--INSERT INTO company_pages VALUES(2,'Airbnb',700000)
--INSERT INTO company_pages VALUES(3,'The Ravit Show',6000)
--INSERT INTO company_pages VALUES(4,'DataLemur',200)
--INSERT INTO company_pages VALUES(5,'YouTube',16000000)
--INSERT INTO company_pages VALUES(6,'DataScience.Vin',4500)
--INSERT INTO company_pages VALUES(7,'Sydney Data Science',1000)
--INSERT INTO company_pages VALUES(8,'Maven Analytics',71000)
--INSERT INTO company_pages VALUES(9,'Ace The Data Science Interview',4479)
--INSERT INTO company_pages VALUES(10,'Amazon Web Services',7341669)
SELECT * FROM personal_profiles
SELECT * FROM employee_company
SELECT * FROM company_pages