Power Creators (Part 2) — SQL Question Asked By LinkedIn

  • Post last modified:September 12, 2022
  • Reading time:3 mins read
Photo by Caspar Camille Rubin on Unsplash

Introduction

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

Question

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

Solution

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

Final Solution

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

Submission

  • Our solution is accepted by platform.

Conclusion

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

Leave a Reply