Reported Posts

Reported Posts

What is this problem about?

The Reported Posts interview question is a SQL aggregation problem. Given a table of post reports (where users can report posts with a reason like "spam" or "pornography"), find the number of posts reported for each report reason on a specific date, while counting each post only once per reason (ignoring duplicate reports of the same post for the same reason on the same day).

Why is this asked in interviews?

Meta asks this problem in interviews because it tests two fundamental SQL skills: filtering by date conditions and using COUNT(DISTINCT ...) to deduplicate. In content moderation systems at Meta, deduplication of reports is critical — multiple users reporting the same post should not inflate counts. It evaluates whether candidates understand when to use DISTINCT inside aggregate functions.

Algorithmic pattern used

The pattern is SQL GROUP BY with COUNT DISTINCT and date filtering. Filter the Reports table for the target date using a WHERE clause. Group by action_reason. Use COUNT(DISTINCT post_id) to count unique posts per reason — this prevents a post reported multiple times by different users from being counted more than once. The query structure:

SELECT action_reason AS report_reason, COUNT(DISTINCT post_id) AS report_count
FROM Reports
WHERE action_date = '2019-07-04'
GROUP BY action_reason;

Example explanation

Reports table (for date 2019-07-04):

post_id	action_reason
1	spam
1	spam
2	spam
3	pornography

After COUNT(DISTINCT post_id) GROUP BY action_reason:

• spam: posts 1 and 2 → count = 2.
• pornography: post 3 → count = 1.

Result:

report_reason	report_count
spam	2
pornography	1

Common mistakes candidates make

• Using COUNT(post_id) instead of COUNT(DISTINCT post_id), which double-counts posts reported multiple times.
• Filtering by the wrong date or using incorrect date comparison syntax.
• Forgetting to GROUP BY the reason column, aggregating everything into one row.
• Using HAVING instead of WHERE for the date filter — WHERE is correct since it filters before aggregation.

Interview preparation tip

For the Reported Posts coding problem, the database interview pattern is COUNT DISTINCT with GROUP BY. This is a common pattern in analytics queries — whenever you need to count unique entities within categories, reach for COUNT(DISTINCT column). Meta interviewers may follow up with "how would the query change if you also needed the percentage of posts removed?" — practice combining COUNT with subqueries or CTEs to handle multi-step aggregations.