Reported Posts - Problem

You are given a table Actions that contains information about user actions on posts.

Table: Actions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| post_id       | int     |
| action_date   | date    |
| action        | enum    |
| extra         | varchar |
+---------------+---------+

This table may have duplicate rows. The action column is an ENUM type with values: ('view', 'like', 'reaction', 'comment', 'report', 'share'). The extra column has optional information about the action, such as a reason for the report or a type of reaction.

Task: Write a solution to report the number of posts reported yesterday for each report reason. Assume today is 2019-07-05.

Return the result table in any order.

Table Schema

Actions
Column Name Type Description
user_id int ID of the user performing the action
post_id int ID of the post being acted upon
action_date date Date when the action was performed
action enum Type of action: view, like, reaction, comment, report, share
extra varchar Additional information like report reason or reaction type
Primary Key: None
Note: Table may contain duplicate rows. For report actions, the extra column contains the reason for the report.

Input & Output

Example 1 — Multiple Report Reasons
Input Table:
user_id post_id action_date action extra
1 1 2019-07-04 view
1 1 2019-07-04 report spam
1 2 2019-07-04 report racism
2 2 2019-07-04 report racism
1 3 2019-07-05 report spam
Output:
report_reason report_count
racism 1
spam 1
💡 Note:

Yesterday (2019-07-04) had 3 report actions: post 1 reported for spam, post 2 reported for racism (by 2 different users). We count distinct posts per reason, so racism: 1 post, spam: 1 post. The report from today (2019-07-05) is excluded.
Example 2 — No Reports Yesterday
Input Table:
user_id post_id action_date action extra
1 1 2019-07-03 report spam
1 2 2019-07-05 report racism
2 3 2019-07-04 view
Output:
report_reason report_count
💡 Note:

No report actions occurred on yesterday (2019-07-04). There was only a view action, so the result is empty.
Example 3 — Same Post Multiple Reasons
Input Table:
user_id post_id action_date action extra
1 1 2019-07-04 report spam
2 1 2019-07-04 report spam
3 2 2019-07-04 report violence
Output:
report_reason report_count
spam 2
violence 1
💡 Note:

Post 1 was reported twice for spam by different users, and post 2 was reported once for violence. The count shows 2 posts total reported for spam and 1 post for violence.

Constraints

  • 1 ≤ user_id, post_id ≤ 1000
  • action_date is a valid date
  • action is one of ('view', 'like', 'reaction', 'comment', 'report', 'share')
  • extra can be NULL or contain additional action information

Visualization

Tap to expand
Reported Posts: Filter and Group AnalysisActions Tableuser_idpost_idactionaction_dateextra11report2019-07-04spam12report2019-07-04racism22report2019-07-04racism13view2019-07-04nullWHEREaction = report ANDaction_date = 2019-07-04GROUP BY extraResultreport_reasonreport_countracism1spam1
Understanding the Visualization
1
Filter
Select only report actions from 2019-07-04
2
Group
Group by report reason (extra column)
3
Count
Count distinct posts per reason
Key Takeaway
🎯 Key Insight: Filter first by specific criteria, then group to aggregate counts efficiently

Related Problems

Asked in
Facebook 23 Instagram 18 Twitter 15
25.4K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen