This Leetcode problem Reported Posts LeetCode Solution is done in SQL.
List of all LeetCode Solution
![Reported Posts LeetCode Solution](https://i0.wp.com/totheinnovation.com/wp-content/uploads/2024/02/LeetCode-Problem-Solution.png?resize=200%2C200&ssl=1)
Reported Posts LeetCode Solution
Table of Contents
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.
Reported Posts LeetCode Solution 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;
Code language: SQL (Structured Query Language) (sql)
Reported Posts LeetCode Solution 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;
Code language: SQL (Structured Query Language) (sql)