LinkedIn Power Creators (Part 2)
The LinkedIn Creator team is looking for power creators who use their personal profile as a company or influencer page. If someone's LinkedIn page has more followers than 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 alphabetical order.
This is the second part of the question, so make sure your start with Part 1 if you haven't completed that yet!
|1||The Data Science Podcast||8,000|
|3||The Ravit Show||6,000|
|9||Ace The Data Science Interview||4479|
This output shows that profile IDs 1-5 are all power creators, meaning that they have more followers than their each of their company pages, whether they work for 1 company or 3.
In contrast to the first part of this question, the assumption here is that a person can work at multiple companies. Since this is the case, we should only compare a person's profile followers against the employer company with the highest follower count.
Before we can analyze the data, we need to combine the and tables that show the relationships between each and their employers.
Using a , the query should look like this:
Click here to learn more about the different SQL JOINs.
Now we have profiles, companies, and their respective follower counts all in one dataset. It's time to determine the companies with the largest follower count per using aggregate function and group by.
Let's wrap this finished query in a common table expression (CTE) so we can reuse it later.
We made it to the final step!
Just like in Part 1, it's time to compare the followers between each profile ID and its most popular employer company. Then, we'll select the profile IDs that have more followers than their respective company.
That's it – well done!