Content Moderation View Metrics

You are tasked with analyzing content moderation data using two provided tables. Your goal is to compute various metrics related to post views and policy violations.

Tables

content_views

This table records user interactions with posts.

• user_id (INTEGER): Identifier for the user who viewed the post.
• post_id (INTEGER): Identifier for the viewed post.
• view_count (INTEGER): The number of views generated by a specific user for a specific post on a given date.
• view_date (STRING): The date of the views in YYYY-MM-DD format.
• Primary Key: (user_id, post_id, view_date)

post_policy_scores

This table stores policy violation probabilities for posts.

• post_id (INTEGER): Identifier for the post.
• violation_type (STRING): The category of policy violation, such as Spam, Scam, Nudity, or Harassment.
• probability_violating (DOUBLE): A model-estimated probability that the post violates the specified policy type.
• Primary Key: (post_id, violation_type)

Relationship

The content_views.post_id column can be joined with post_policy_scores.post_id.

Assumptions

• The view_date string should be treated as a UTC date and cast to a DATE type when necessary for calculations.
• Define analysis_date as the maximum view_date present in the content_views table.
• Date windows for "last 7 days" and "last 30 days" are inclusive and end on the analysis_date.
• A post is considered to belong to a specific violation_type if its probability_violating for that type is greater than 0.5.
• For monthly aggregations, use the calendar month derived from the view_date.

Tasks

Write SQL queries to address the following analytical requirements:

1. Distinct Posts with High View Count

Count the number of distinct posts that received a total of at least 10 views within the last 7 days.

• The filter for "at least 10 total views" must be applied after aggregating views by post_id.
• Required Output Column: num_posts

2. Nudity View Prevalence

Compute the prevalence of views on posts classified as 'Nudity' within the last 30 days. This is defined as: Total views on posts classified as Nudity / Total views on all posts in that window

• Required Output Columns: nudity_views, total_views, nudity_view_prevalence

3. Monthly Violation-Type Distribution

For each calendar month and each violation_type, report the following metrics. This analysis should only include post-violation pairs where probability_violating > 0.5 and the post received at least one view in that specific month.

• month: The calendar month.
• violation_type: The policy violation category.
• flagged_post_count: The number of distinct posts in that month assigned to that specific violation_type.
• distribution_pct: The percentage distribution, calculated as flagged_post_count / total flagged post-violation pairs in that month.