Yesterday Reported Post Reason Summary

Easy

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 1

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 2

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 3

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.

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

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 |

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

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 |