Identify Duplicate Posts by User and Date

posts

+---------+---------+---------------------+---------------+
| post_id | user_id | created_at          | content       |
+---------+---------+---------------------+---------------+
| 101     | 1       | 2024-05-01 10:00:00 | hello world   |
| 102     | 1       | 2024-05-01 10:00:00 | hello world   |
| 103     | 2       | 2024-05-02 12:00:00 | good morning  |
| 104     | 1       | 2024-05-03 09:00:00 | hello world   |
| 105     | 2       | 2024-05-02 12:05:00 | good morning  |
+---------+---------+---------------------+---------------+

Scenario

Meta wants to detect duplicate posts so they can surface only unique content in users’ feeds.

Question

Given table posts(post_id, user_id, created_at, content), write SQL that returns user_id, content, duplicate_count for posts where the same user posted identical content on the same calendar day (duplicate_count ≥ 2).

Hints

Group by user_id, content, DATE(created_at); HAVING COUNT(*) > 1.

Detect duplicate posts by user and calendar day

Table posts

column	type	constraints
post_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
created_at	TIMESTAMP	NOT NULL
content	VARCHAR(255)	NOT NULL

Sample data:

content	created_at	post_id	user_id
hello world	2024-05-01 10:00:00	101	1
hello world	2024-05-01 10:00:00	102	1
good morning	2024-05-02 12:00:00	103	2
hello world	2024-05-03 09:00:00	104	1
good morning	2024-05-02 12:05:00	105	2

Expected output

One row per (user_id, content) for each calendar day where the same user posted identical content at least twice on that day. Returns user_id, content, and duplicate_count (the number of such posts on that day).

content	duplicate_count	user_id
hello world	2	1
good morning	2	2

Hints

1. Extract the calendar day from created_at, for example using DATE(created_at).
2. Group by user_id, content, and DATE(created_at) and filter groups with HAVING COUNT(*) >= 2.