Last updated on March 10th, 2025 at 10:53 pm

Here, we see the Reported Posts LeetCode Solution. This Leetcode problem is solved using MySQL and Pandas.

List of all LeetCode Solution

Level of Question

Easy

Reported Posts LeetCode Solution

1. Problem Statement

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

There is no primary key for this table, it may have duplicate rows.
The action column is an ENUM type of (‘view’, ‘like’, ‘reaction’, ‘comment’,’report’, ‘share’).
The extra column has optional information about the action such as a reason for report or a type of reaction.

Write an SQL query that reports the number of posts reported yesterday for each report reason. Assume today is 2019-07-05.

The result format is in the following example.

Example 1:
Input:

user_id	post_id	action_date	action	extra
1	1	2019-07-01	view	null
1	1	2019-07-01	like	null
1	1	2019-07-01	share	null
2	4	2019-07-04	view	null
2	4	2019-07-04	report	spam
3	4	2019-07-04	view	null
3	4	2019-07-04	report	spam
4	3	2019-07-02	view	null
4	3	2019-07-02	report	spam
5	2	2019-07-04	view	null
5	2	2019-07-04	report	racism
5	5	2019-07-04	view	null
5	5	2019-07-04	report	racism

Output:

report_reason	report_count
spam	1
racism	2

Explanation:
Note that we only care about report reasons with non zero number of reports.

2. Code Implementation in Different Languages

2.1 Reported Posts MySQL

select 
  extra as report_reason, 
  count(*) as report_count 
from 
  (
    select 
      post_id, 
      extra 
    from 
      Actions 
    where 
      action_date = '2019-07-04' 
      and action = 'report' 
    group by 
      post_id, 
      extra
  ) as t 
group by 
  t.extra;

2.2 Reported Posts MySQL (Another approach)

select 
  extra as report_reason, 
  count(distinct post_id) as report_count 
from 
  Actions 
where 
  action_date = '2019-07-04' 
  and action = 'report' 
group by 
  extra;