Daily Spam Report Clearance Rate

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:

1. Eligible events are rows in content_actions where:
   • action = 'report'
   • reason = 'spam'
2. Eligible days are dates with at least one eligible event.
3. 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
4. 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)