Drive-By Commenters Who Never Liked the Post — SQL Problem
Spam rings often comment on posts without liking them. The integrity team flags users who commented on at least two different posts by other people that…
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #435
Problem statement
Spam rings often comment on posts without liking them. The integrity team flags users who commented on at least two different posts by other people that they did not like. Comments on one's own posts never count.
Return the columns user_id and unliked_posts (the number of distinct such posts), in any order. Several comments on the same post count once.
Tables
Table: posts
| Column | Type |
|---|---|
| post_id | int |
| author_id | int |
Primary key: post_id.
One row per post.
Table: comments
| Column | Type |
|---|---|
| comment_id | int |
| post_id | int |
| user_id | int |
| commented_at | datetime |
Primary key: comment_id.
post_id is always a row of posts; user_id is the commenter.
Table: likes
| Column | Type |
|---|---|
| post_id | int |
| user_id | int |
Primary key: post_id, user_id.
One row per (post, user who liked it).
Examples
Example 1
posts
| post_id | author_id |
|---|---|
| 1 | 50 |
| 2 | 50 |
| 3 | 51 |
| 4 | 52 |
comments
| comment_id | post_id | user_id | commented_at |
|---|---|---|---|
| 1 | 1 | 60 | 2025-05-01 10:00:00 |
| 2 | 1 | 60 | 2025-05-01 10:01:00 |
| 3 | 3 | 60 | 2025-05-01 10:05:00 |
| 4 | 2 | 61 | 2025-05-02 09:00:00 |
| 5 | 4 | 61 | 2025-05-02 09:30:00 |
| 6 | 1 | 50 | 2025-05-02 11:00:00 |
| 7 | 2 | 50 | 2025-05-02 11:05:00 |
| 8 | 3 | 62 | 2025-05-03 08:00:00 |
| 9 | 4 | 62 | 2025-05-03 08:10:00 |
likes
| post_id | user_id |
|---|---|
| 2 | 61 |
| 3 | 62 |
| 1 | 62 |
Output
| user_id | unliked_posts |
|---|---|
| 60 | 2 |
How to solve Drive-By Commenters Who Never Liked the Post
Work at the level of single comments first. A comment qualifies when its post was written by someone else (join posts and compare author_id with the commenter) and when there is no like from the same user on the same post — an anti join on the pair (post_id, user_id), written with NOT EXISTS or as a LEFT JOIN that keeps only unmatched rows.
Then aggregate: group the qualifying comments by commenter and count DISTINCT post_id, because a spammer posting three comments on one post has still hit only one post. HAVING COUNT(DISTINCT post_id) >= 2 applies the threshold to the group.
The third query deduplicates (user_id, post_id) pairs first and uses a row-value NOT IN against likes; that is safe because both columns of the likes primary key are non-NULL. All three are linear with the composite index on likes.
Reference solution (MySQL)
SELECT c.user_id, COUNT(DISTINCT c.post_id) AS unliked_posts
FROM comments c
JOIN posts p ON p.post_id = c.post_id
WHERE p.author_id <> c.user_id
AND NOT EXISTS (SELECT 1 FROM likes l WHERE l.post_id = c.post_id AND l.user_id = c.user_id)
GROUP BY c.user_id
HAVING COUNT(DISTINCT c.post_id) >= 2Another way
SELECT c.user_id, COUNT(DISTINCT c.post_id) AS unliked_posts
FROM comments c
JOIN posts p ON p.post_id = c.post_id AND p.author_id <> c.user_id
LEFT JOIN likes l ON l.post_id = c.post_id AND l.user_id = c.user_id
WHERE l.post_id IS NULL
GROUP BY c.user_id
HAVING COUNT(DISTINCT c.post_id) >= 2Another way
SELECT user_id, COUNT(*) AS unliked_posts FROM (
SELECT DISTINCT c.user_id, c.post_id FROM comments c
WHERE c.user_id <> (SELECT author_id FROM posts p WHERE p.post_id = c.post_id)
AND (c.post_id, c.user_id) NOT IN (SELECT post_id, user_id FROM likes)
) t GROUP BY user_id HAVING COUNT(*) >= 2← Moderation Outcomes by Report Reason · Sharp Follower Drops Between Daily Snapshots →