Most Liked Posts
Which posts have received the most likes — show the top posts with creator and content preview?
- Joins
- String functions
- Sorting
- Top-N
Challenge brief
Understand the request
Feed Ranking Team is tuning the content feed algorithm and needs to identify posts with the highest organic like counts.
List posts ordered by likes_count descending, showing post ID, creator username, a 100-character content preview, and likes count.
Return
- post_id
- username
- post_content_preview (first 100 chars of content)
- likes_count
Constraints
- Include posts from every visibility level
- Return at most 10 posts
- Resolve equal like counts by the lower post ID
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
posts
post_idINTEGERuser_idINTEGERcontentTEXTlikes_countINTEGER
users
user_idINTEGERusernameVARCHAR(50)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Post data is in posts, creator username is in users. Connect on user_id. Use SUBSTR(content, 1, 100) to truncate long posts to a 100-character preview.
Hint 2
INNER JOIN posts to users on user_id. SELECT SUBSTR(p.content, 1, 100) AS post_content_preview. ORDER BY p.likes_count DESC LIMIT 10.
Hint 3
Start with the tables that establish the result grain for question 3, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT p.post_id, u.username, SUBSTR(p.content, 1, 100) AS post_content_preview, p.likes_count FROM posts p INNER JOIN users u ON p.user_id = u.user_id ORDER BY p.likes_count DESC, p.post_id ASC LIMIT 10;Why this works
Join each post to its creator, sort by likes descending, and use post_id as a stable tie-break before applying the row limit.
Success check
6 posts — top is jane_smith's 'Thoughts on remote work' (203 likes)
Expected result
Use this output to verify values, aliases, ordering, and row count.
| post_id | username | post_content_preview | likes_count |
|---|---|---|---|
| 106 | jane_smith | Thoughts on remote work | 203 |
| 105 | sarah_jones | Check out my new design | 156 |
| 102 | jane_smith | Beautiful sunset today | 120 |
| 103 | mike_wilson | New blog post on AI | 89 |
| 104 | john_doe | Weekend vibes | 67 |
| 101 | john_doe | Just finished a great project! | 45 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Google
Independent Google-style search, advertising, user-engagement, and video-product SQL practice.
Netflix
Independent Netflix-style streaming, subscription, catalog, ratings, and engagement SQL practice.
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Return to the complete interview preparation experience.