Reported Posts LeetCode Solution

Last updated on October 9th, 2024 at 10:32 pm

This Leetcode problem Reported Posts LeetCode Solution is done in SQL.

List of all LeetCode Solution

Level of Question

Easy

Reported Posts LeetCode Solution

Reported Posts LeetCode Solution

Problem Statement

Column NameType
user_idint
post_idint
action_datedate
actionenum
extravarchar
Table: Actions

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_idaction_dateactionextra
112019-07-01viewnull
112019-07-01likenull
112019-07-01sharenull
242019-07-04viewnull
242019-07-04reportspam
342019-07-04viewnull
342019-07-04reportspam
432019-07-02viewnull
432019-07-02reportspam
522019-07-04viewnull
522019-07-04reportracism
552019-07-04viewnull
552019-07-04reportracism
Actions table:

Output:

report_reasonreport_count
spam1
racism2

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

1. 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;

2. 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;
Scroll to Top