Instagram Likes
Instagram is a social media platform that allows users to share photos and videos with their followers. Users can upload images and videos to their profile. Other users follow their accounts to see their posts in their feed, and they can also like, comment, and share content.
You're given two tables, users and posts, with the following columns:
Write a SQL query that returns a table (with columns username and num_popular_posts) with the top 3 users who have created the highest number of posts with at least 100 likes.
To solve this problem, we'll first need to aggregate over the posts table to count the number of posts having greater than 100 likes. We've written this as a CTE popular_posts for readability. Then we join this with the users table to return more information about the users, sort in descending order, and return the top three users with limit.
with popular_posts as (
select
user_id,
count(*) as num_popular_posts
from posts
where likes >= 100
group by user_id
),
final as (
select
users.username,
popular_posts.num_popular_posts
from users
inner join popular_posts on
users.user_id = popular_posts.user_id
order by popular_posts.num_popular_posts desc
)
select * from final
limit 3;
Here's a simpler solution:
select u.username , count(p.post_id) as count_posts from posts as p join users as u on p.user_id = u.user_id where p.likes >= 100 group by 1 order by 2 desc, 1 asc limit 3