Posts With No Likes From Anyone but the Author

Easy
424.

Posts With No Likes From Anyone but the Author

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

ColumnTypeValues
post_idint
author_idint
post_typeenumphoto, reel, carousel, text
posted_atdatetime

Primary key: post_id.

One row per post.

Table: likes

ColumnType
post_idint
user_idint
liked_atdatetime

Primary key (together): post_id, user_id.

One row per (post, user who liked it). post_id is always a row of posts.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

posts

post_idauthor_idpost_typeposted_at
110photo2025-01-04 09:12:00
211reel2025-01-04 10:30:00
310carousel2025-01-05 18:45:00
412text2025-01-06 08:00:00
513reel2025-01-06 21:10:00

likes

post_iduser_idliked_at
1112025-01-04 09:30:00
1122025-01-04 11:02:00
2112025-01-04 10:31:00
3102025-01-05 18:46:00
3132025-01-05 20:00:00
5132025-01-06 21:11:00

Output

post_idauthor_id
211
412
513
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
post_idint
author_idint
post_typeenum(photo, reel, carousel, text)
posted_atdatetime

Primary key: post_id.

One row per post.

Table: likes

ColumnType
post_idint
user_idint
liked_atdatetime

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_idauthor_idpost_typeposted_at
110photo2025-01-04 09:12:00
211reel2025-01-04 10:30:00
310carousel2025-01-05 18:45:00
412text2025-01-06 08:00:00
513reel2025-01-06 21:10:00

likes

post_iduser_idliked_at
1112025-01-04 09:30:00
1122025-01-04 11:02:00
2112025-01-04 10:31:00
3102025-01-05 18:46:00
3132025-01-05 20:00:00
5132025-01-06 21:11:00

Output

post_idauthor_id
211
412
513

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 NULL

Another 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 →