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 - SQL Solution INPUT: Actions Table user_id post_id action extra date 1 1 view null 07-01 1 1 report spam 07-04 2 2 view null 07-04 2 3 report spam 07-04 3 4 report racism 07-04 4 5 report spam 07-03 = Reports on 2019-07-04 Today: 2019-07-05 Yesterday: 2019-07-04 July 2019 04 ALGORITHM STEPS 1 Filter by Date WHERE action_date = '2019-07-04' 2 Filter by Action WHERE action = 'report' 3 Group by Reason GROUP BY extra (report reason) 4 Count Posts COUNT(DISTINCT post_id) SELECT extra AS report_reason, COUNT(DISTINCT post_id) AS report_count FROM Actions WHERE action='report' AND action_date='2019-07-04' GROUP BY extra; FINAL RESULT report_reason count spam 2 racism 1 Breakdown: SPAM Reports: P1 P3 = 2 posts RACISM Reports: P4 = 1 post OK - Query Complete Key Insight: 1. Use DISTINCT to count unique posts (same post can be reported multiple times by different users) 2. Filter by action='report' AND yesterday's date using DATE_SUB(CURDATE(), INTERVAL 1 DAY) 3. Group by 'extra' column which contains the report reason (spam, racism, etc.) TutorialsPoint - Reported Posts | Optimal Solution

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