Posts With No Likes From Anyone but the Author — SQL Problem
The feed-ranking team is studying posts that got zero outside engagement. Authors are allowed to like their own posts, but a self-like does not count.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #424
Problem statement
The feed-ranking team is studying posts that got zero outside engagement. Authors are allowed to like their own posts, but a self-like does not count.
Return every post that has no like from any user other than its author, with the columns post_id and author_id. A post with only its author's like is in the answer; a post with no likes at all is too. Return the rows in any order.
Tables
Table: posts
| Column | Type |
|---|---|
| post_id | int |
| author_id | int |
| post_type | enum(photo, reel, carousel, text) |
| posted_at | datetime |
Primary key: post_id.
One row per post.
Table: likes
| Column | Type |
|---|---|
| post_id | int |
| user_id | int |
| liked_at | datetime |
Primary key: post_id, user_id.
One row per (post, user who liked it). post_id is always a row of posts.
Examples
Example 1
posts
| post_id | author_id | post_type | posted_at |
|---|---|---|---|
| 1 | 10 | photo | 2025-01-04 09:12:00 |
| 2 | 11 | reel | 2025-01-04 10:30:00 |
| 3 | 10 | carousel | 2025-01-05 18:45:00 |
| 4 | 12 | text | 2025-01-06 08:00:00 |
| 5 | 13 | reel | 2025-01-06 21:10:00 |
likes
| post_id | user_id | liked_at |
|---|---|---|
| 1 | 11 | 2025-01-04 09:30:00 |
| 1 | 12 | 2025-01-04 11:02:00 |
| 2 | 11 | 2025-01-04 10:31:00 |
| 3 | 10 | 2025-01-05 18:46:00 |
| 3 | 13 | 2025-01-05 20:00:00 |
| 5 | 13 | 2025-01-06 21:11:00 |
Output
| post_id | author_id |
|---|---|
| 2 | 11 |
| 4 | 12 |
| 5 | 13 |
How to solve Posts With No Likes From Anyone but the Author
The answer is an anti join with an extra condition: keep a post when no row of likes both belongs to it and comes from someone other than its author. NOT EXISTS states that literally — the correlated subquery looks for a like on p.post_id whose user_id differs from p.author_id, and the post survives when it finds none.
The LEFT JOIN version must carry the self-like rule in the ON clause. If you joined on post_id alone and then wrote WHERE l.user_id <> p.author_id OR l.post_id IS NULL, a post liked only by its author would match its self-like row, fail the filter and vanish — the classic mistake of turning an outer join's condition into a WHERE filter. With the rule in ON, a post whose only like is a self-like gets no partner row and comes out NULL-extended, which IS NULL keeps.
The NOT IN form is safe here because likes.post_id is part of the primary key and never NULL. All three are one pass over posts with an index lookup into likes.
Reference solution (MySQL)
SELECT p.post_id, p.author_id
FROM posts p
WHERE NOT EXISTS (
SELECT 1 FROM likes l
WHERE l.post_id = p.post_id AND l.user_id <> p.author_id
)Another way
SELECT p.post_id, p.author_id FROM posts p LEFT JOIN likes l ON l.post_id = p.post_id AND l.user_id <> p.author_id WHERE l.post_id IS NULLAnother way
SELECT post_id, author_id FROM posts WHERE post_id NOT IN (SELECT l.post_id FROM likes l JOIN posts q ON q.post_id = l.post_id WHERE l.user_id <> q.author_id)← Verified Creators With Over One Lakh Followers · Stories Posted and Total Story Views per User →