1113 Reported Posts

Description

Table: Actions

• This table may have duplicate rows.
• The action column is an ENUM (category) type of (‘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.
• extra is never NULL.

Problem Statement

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. The result format is in the following example.

Example 1:

Input:

• Actions table:

Output:

Explanation:

• Note that we only care about report reasons with non-zero number of reports.

Solution

Here, you need to report the number of posts reported yesterday for each report reason. You can filter the action_date to filter the last 1 day posts using action_date >= DATE_SUB('2019-07-05', INTERVAL 1 DAY) AND action_date < '2019-07-05'. For each report reason, you need to count the number of unique post_id. If you simply count number of records, you may get wrong result because the post_id=4 was reported twice for the same reason (spam). That’s why you should use COUNT(DISTINCT post_id).

1SELECT extra AS report_reason, COUNT(DISTINCT post_id) AS report_count
2    FROM Actions
3    WHERE action='report' AND action_date >= DATE_SUB('2019-07-05', INTERVAL 1 DAY) AND action_date < '2019-07-05'
4    GROUP BY report_reason;