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

Originally Published in https://asyncq.com/

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

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.

-> Follow me on Medium & LinkedIn
-> Find more blogs @https://asyncq.com/

-> For the latest data engineering articles subscribe to Data Engineering Bulletin

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Suraj Mishra

Suraj Mishra

323 Followers

Google Cloud Certified Professional Data Engineer | Backend Engineering @ Rakuten Card | Blogs @https://asyncq.com/