- In this article we will solve Power Creators (Part 2) SQL question which is asked by LinkedIn as per DataLemur website.
- We have solved part 1 previously
If you don’t know DatLemur then please do visit , it’s one of the best website to practice SQL questions and improve your SQL skills.
- We have been given personal_profiles input table along with employe_company and company_page table.
- We have been given a taks to find out power creators who has followers more than the company they work for.
- Each creators can work for more than one company.
- At first , we need to join all three input table based on common key.
- We will join personal_profile with employee_company on profile_id as common key.
- Then the resultant table will be joined with company_pages on company_id column as common key.
- If we execute above query we get the denormalized table as shown below.
- Once our base table is ready, we can group by profile_id and profile_followers. The reason we are doing this because there maybe cases where one employee can work for multiple companies.
- So if we do group by and find max case where the company followers is greater than profile followers and that profile_id would be our output.
- Here is the list of profile_ids that were having followers more than any of the company they work for.
- Here is the final solution of the problem
with cte as ( select pf.name, pf.followers profile_followers, pf.profile_id, ec.company_id, cp.name company_name, cp.followers as company_followers from personal_profiles pf join employee_company ec on pf.profile_id = ec.personal_profile_id join company_pages cp on ec.company_id = cp.company_id ) select profile_id from cte group by profile_id, profile_followers having max(company_followers) < profile_followers order by profile_id asc;
- Our solution is accepted by platform.
- In this article we solved sql question asked by LinkedIn. We solved part 1 of the problem earlier. In this question only difference is that creator can work for more than 1 company and while finding out the power creator . In this question we used CTE (Common table expression) to first create base table and then used having clause to filter out only power creators.