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

  • Post last modified:September 11, 2022
  • Reading time:2 mins read

Photo by Caspar Camille Rubin on Unsplash

Introduction

  • In this article we will solve Power Creators (Part 1) SQL question which is asked by LinkedIn as per DataLemur website.

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_profile pages and company pages.
  • If someone’s personal profile has more followers than company they work for then they are power creators.
  • We need to return the ID’s of such power creator.
  • As we can see below profile_id has personal followers more than company followers.

Solution

  • At first we can see that there are two tables company_page and profile_page. we can see that both the table are connected with employer_id and company_id.
  • So we can use this relation to our advantage and join these two tables to get the final base table ready for querying the output.
  • Each profile now has company info along with it. we can see for profile id 1 , followers 92k and company followers are 200. So this user would be power creator.
  • Now all we have to do is to filter out all the profile_ids where personal followers are more than company followers, then we can order them by profile id as expected in the question.
\

Final Solution

Here is the final solution for the problem.

select pf.profile_id from personal_profiles pf 
join company_pages cp
on pf.employer_id = cp.company_id
where pf.followers > cp.followers

Submission

  • Our solution is accepted by the platform.

Conclusion

  • In this article we solve sql question asked by LinkedIn. We used inner join to join to tables based on common id and then finally filtered out all the cases which were asked in the question.

Leave a Reply