Drive-By Commenters Who Never Liked the Post

Medium
435.

Drive-By Commenters Who Never Liked the Post

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

ColumnType
post_idint
author_idint

Primary key: post_id.

One row per post.

Table: comments

ColumnType
comment_idint
post_idint
user_idint
commented_atdatetime

Primary key: comment_id.

post_id is always a row of posts; user_id is the commenter.

Table: likes

ColumnType
post_idint
user_idint

Primary key (together): post_id, user_id.

One row per (post, user who liked it).

Example

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

posts

post_idauthor_id
150
250
351
452

comments

comment_idpost_iduser_idcommented_at
11602025-05-01 10:00:00
21602025-05-01 10:01:00
33602025-05-01 10:05:00
42612025-05-02 09:00:00
54612025-05-02 09:30:00
61502025-05-02 11:00:00
72502025-05-02 11:05:00
83622025-05-03 08:00:00
94622025-05-03 08:10:00

likes

post_iduser_id
261
362
162

Output

user_idunliked_posts
602
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.

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

ColumnType
post_idint
author_idint

Primary key: post_id.

One row per post.

Table: comments

ColumnType
comment_idint
post_idint
user_idint
commented_atdatetime

Primary key: comment_id.

post_id is always a row of posts; user_id is the commenter.

Table: likes

ColumnType
post_idint
user_idint

Primary key: post_id, user_id.

One row per (post, user who liked it).

Examples

Example 1

posts

post_idauthor_id
150
250
351
452

comments

comment_idpost_iduser_idcommented_at
11602025-05-01 10:00:00
21602025-05-01 10:01:00
33602025-05-01 10:05:00
42612025-05-02 09:00:00
54612025-05-02 09:30:00
61502025-05-02 11:00:00
72502025-05-02 11:05:00
83622025-05-03 08:00:00
94622025-05-03 08:10:00

likes

post_iduser_id
261
362
162

Output

user_idunliked_posts
602

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) >= 2

Another 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) >= 2

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