Blog

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.

Bonus Tip

• If you want to upskill your SQL interview game, you can definitely check out this bestseller course