Daily Spam Report Clearance Rate

Medium

Your moderation analytics pipeline stores user events in content_actions and final takedown decisions in removed_posts. Table: content_actions user_id: user who triggered the event post_id: content item identifier action_date: date of the event action: one of ('view', 'like', 'reaction', 'comment', 'report', 'share') reason: optional detail (for reports this may include labels like 'spam') Table: removed_posts post_id: content identifier that was eventually removed removed_on: removal date Task: Compute the average daily percentage of spam-reported posts that ended up removed. Precise metric definition: Eligible events are rows in content_actions where: action = 'report' reason = 'spam' Eligible days are dates with at least one eligible event. For each eligible day d: reported_d = count of distinct post_id in eligible events on day d removed_d = count of those distinct post_id that exist in removed_posts daily_percent_d = 100 * removed_d / reported_d Final output: average_daily_percent = ROUND(AVG(daily_percent_d), 2) Important notes: Count distinct post_id per day for the denominator. If a post is spam-reported multiple times on the same day, it counts once for that day. remove date is not part of the metric; only post presence in removed_posts matters. If there are no eligible days, return NULL. Return exactly one row with one column: average_daily_percent Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

content_actions:
| user_id | post_id | action_date | action | reason |
|---------|---------|-------------|--------|--------|
| 1       | 101     | 2025-01-01  | report | spam   |
| 2       | 102     | 2025-01-01  | report | spam   |
| 3       | 102     | 2025-01-01  | report | spam   |
| 4       | 104     | 2025-01-02  | report | spam   |
| 5       | 105     | 2025-01-02  | report | spam   |
| 6       | 105     | 2025-01-02  | comment| spam   |
| 7       | 106     | 2025-01-03  | report | abuse  |

removed_posts:
| post_id | removed_on  |
|---------|-------------|
| 101     | 2025-01-09  |
| 102     | 2025-01-10  |
| 104     | 2025-01-11  |

Output

[
  {"average_daily_percent":75.0}
]

Explanation

Eligible days are 2025-01-01 and 2025-01-02. Day 1 has distinct spam posts {101,102} and both are removed => 100%. Day 2 has {104,105} and only 104 is removed => 50%. Average is (100 + 50) / 2 = 75.00.

Example 2

Input

content_actions:
| user_id | post_id | action_date | action | reason |
|---------|---------|-------------|--------|--------|
| 10      | 900     | 2025-02-01  | report | abuse  |
| 11      | 901     | 2025-02-01  | view   | null   |
| 12      | 902     | 2025-02-02  | comment| spam   |

removed_posts:
| post_id | removed_on  |
|---------|-------------|
| 900     | 2025-02-10  |
| 902     | 2025-02-10  |

Output

[
  {"average_daily_percent":null}
]

Explanation

There are no rows where action='report' and reason='spam', so there are no eligible days and the metric is NULL.

Example 3

Input

content_actions:
| user_id | post_id | action_date | action | reason |
|---------|---------|-------------|--------|--------|
| 30      | 201     | 2025-03-01  | report | spam   |
| 31      | 202     | 2025-03-01  | report | spam   |
| 32      | 201     | 2025-03-02  | report | spam   |

removed_posts:
| post_id | removed_on  |
|---------|-------------|
| 201     | 2025-03-08  |

Output

[
  {"average_daily_percent":75.0}
]

Explanation

On 2025-03-01, 1 of 2 distinct spam-reported posts was removed => 50%. On 2025-03-02, 1 of 1 was removed => 100%. Average is 75.00.

Constraints

0 <= number of rows in content_actions <= 100000
0 <= number of rows in removed_posts <= 100000
1 <= user_id, post_id <= 1000000000
action_date and removed_on use YYYY-MM-DD format
content_actions may contain duplicate rows
removed_posts.post_id is unique
Only rows with action = 'report' and reason = 'spam' contribute to the metric
Use distinct post_id per action_date for daily denominator
If no eligible day exists, output NULL
Return exactly one row with one column: average_daily_percent
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a one-row DataFrame with column average_daily_percent

content_actions: | user_id | post_id | action_date | action | reason | |---------|---------|-------------|--------|--------| | 1 | 101 | 2025-01-01 | report | spam | | 2 | 102 | 2025-01-01 | report | spam | | 3 | 102 | 2025-01-01 | report | spam | | 4 | 104 | 2025-01-02 | report | spam | | 5 | 105 | 2025-01-02 | report | spam | | 6 | 105 | 2025-01-02 | comment| spam | | 7 | 106 | 2025-01-03 | report | abuse | removed_posts: | post_id | removed_on | |---------|-------------| | 101 | 2025-01-09 | | 102 | 2025-01-10 | | 104 | 2025-01-11 |

content_actions: | user_id | post_id | action_date | action | reason | |---------|---------|-------------|--------|--------| | 10 | 900 | 2025-02-01 | report | abuse | | 11 | 901 | 2025-02-01 | view | null | | 12 | 902 | 2025-02-02 | comment| spam | removed_posts: | post_id | removed_on | |---------|-------------| | 900 | 2025-02-10 | | 902 | 2025-02-10 |

content_actions: | user_id | post_id | action_date | action | reason | |---------|---------|-------------|--------|--------| | 30 | 201 | 2025-03-01 | report | spam | | 31 | 202 | 2025-03-01 | report | spam | | 32 | 201 | 2025-03-02 | report | spam | removed_posts: | post_id | removed_on | |---------|-------------| | 201 | 2025-03-08 |