Top 3 Most Liked Posts
ASKED IN INTERVIEWInstagram
Problem Statement
Instagram creators need to track which of their content performs the best to optimize their engagement strategy. Your task is to find the top 3 posts for user 101 that have received the highest number of likes.
Write an SQL query to retrieve the post ID and the corresponding like count for these top-performing posts.
Rules:
- Only consider posts belonging to user_id 101.
- Output columns: post_id, like_count.
- The results must be sorted by like_count in descending order.
- If two posts have the same like count, sort them by post_id in ascending order as a tie-breaker.
Tests your understanding of
Filtering, Sorting and Limiting
Input Tables
posts
| post_id(INTEGER) | user_id(INTEGER) | like_count(INTEGER) | content_type(VARCHAR) |
|---|---|---|---|
| 1 | 101 | 150 | Image |
| 2 | 101 | 300 | Reel |
| 3 | 102 | 500 | Image |
| 4 | 101 | 450 | Video |
| 5 | 101 | 200 | Image |
| 6 | 101 | 450 | Reel |
users
| user_id(INTEGER) | username(VARCHAR) |
|---|---|
| 101 | travel_explorer |
| 102 | foodie_vibes |
| 103 | tech_reviews |
post_metrics
| post_id(INTEGER) | share_count(INTEGER) |
|---|---|
| 1 | 10 |
| 2 | 45 |
| 4 | 102 |
Expected Output
| post_id(INTEGER) | like_count(INTEGER) |
|---|---|
| 4 | 450 |
| 6 | 450 |
| 2 | 300 |
Tags
EasyASKED IN INTERVIEWFilteringSortingLimiting
10-15 min
78%