Yesterday Reported Post Reason Summary

EASY10 pts

A trust-and-safety analytics team stores user activity in one table:

Actions columns:

user_id: user who performed the action
post_id: post the action targets
action_date: date of the event
action: one of ('view', 'like', 'reaction', 'comment', 'report', 'share')
extra: optional metadata; for report rows, this is the report reason

Task:

Assume today is 2019-07-05 (so yesterday is 2019-07-04).
Keep only rows where action = 'report' and action_date = '2019-07-04'.
For each report reason, count how many DISTINCT post_id values were reported.

Output requirements:

Return exactly two columns:
- report_reason
- report_count
report_count must represent distinct posts per reason.
Result order is not important.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Actions:
| user_id | post_id | action_date | action | extra  |
|---------|---------|-------------|--------|--------|
| 1       | 1       | 2019-07-01  | view   | null   |
| 2       | 4       | 2019-07-04  | report | spam   |
| 3       | 4       | 2019-07-04  | report | spam   |
| 5       | 2       | 2019-07-04  | report | racism |
| 5       | 5       | 2019-07-04  | report | racism |

Output

[
  {"report_reason":"spam","report_count":1},
  {"report_reason":"racism","report_count":2}
]

Explanation

Spam has one distinct post (post 4) even though it appears in multiple rows. Racism appears on posts 2 and 5, so its distinct-post count is 2.

Example

Input

Actions:
| user_id | post_id | action_date | action | extra |
|---------|---------|-------------|--------|-------|
| 8       | 100     | 2019-07-03  | report | spam  |
| 9       | 101     | 2019-07-04  | like   | null  |

Output

[]

Explanation

No row satisfies both yesterday's date and action='report', so the summary is empty.

Example

Input

Actions:
| user_id | post_id | action_date | action | extra         |
|---------|---------|-------------|--------|---------------|
| 1       | 7       | 2019-07-04  | report | misinformation|
| 2       | 7       | 2019-07-04  | report | misinformation|
| 3       | 7       | 2019-07-04  | report | harassment    |
| 4       | 8       | 2019-07-04  | report | harassment    |

Output

[
  {"report_reason":"misinformation","report_count":1},
  {"report_reason":"harassment","report_count":2}
]

Explanation

A post can contribute to multiple reason groups if it was reported under multiple reasons.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Actions <= 10,000
1 <= user_id, post_id <= 1,000,000,000
action is one of: view, like, reaction, comment, report, share
action_date is a valid date string
extra may be null
Actions may contain duplicate rows
Count DISTINCT post_id per report_reason for action_date = '2019-07-04' and action = 'report'
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: report_reason, report_count

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

actions =

[{"user_id":1,"post_id":1,"action_date":"2019-07-01","action":"view","extra":null},{"user_id":1,"post_id":1,"action_date":"2019-07-01","action":"like","extra":null},{"user_id":1,"post_id":1,"action_date":"2019-07-01","action":"share","extra":null},{"user_id":2,"post_id":4,"action_date":"2019-07-04","action":"view","extra":null},{"user_id":2,"post_id":4,"action_date":"2019-07-04","action":"report","extra":"spam"},{"user_id":3,"post_id":4,"action_date":"2019-07-04","action":"view","extra":null},{"user_id":3,"post_id":4,"action_date":"2019-07-04","action":"report","extra":"spam"},{"user_id":4,"post_id":3,"action_date":"2019-07-02","action":"report","extra":"spam"},{"user_id":5,"post_id":2,"action_date":"2019-07-04","action":"report","extra":"racism"},{"user_id":5,"post_id":5,"action_date":"2019-07-04","action":"report","extra":"racism"}]

Yesterday Reported Post Reason Summary

Hints

Yesterday Reported Post Reason Summary

Hints