Problem Description

Assume the following two tables are available:

post_views

• user_id INTEGER: ID of the user who viewed the post
• post_id INTEGER: ID of the post
• view_count INTEGER: Number of views generated by that user for that post on that date
• view_date STRING: View date in YYYY-MM-DD format

Primary key: (user_id, post_id, view_date)

post_policy_scores

• post_id INTEGER: ID of the post
• violation_type STRING: Policy category such as Spam, Scam, Nudity, or Harassment
• probability_violating DOUBLE: Model-estimated probability that the post violates the policy for that violation type

Primary key: (post_id, violation_type)

Relationship: One post can have multiple rows in post_policy_scores, one per possible violation type.

Assumptions:

• All time windows are inclusive.
• Dates should be interpreted in UTC.
• The reference date for all calculations is the maximum view_date found in the post_views table.

Tasks

Write SQL queries to perform the following tasks:

1. Count Distinct Posts with High Views (Last 7 Days):

   • Count how many distinct posts have at least 10 total views in the last 7 days. The view_count should be aggregated across all users and all dates within the 7-day window.
   • Required output column: posts_with_at_least_10_views_7d.

2. Compute Nudity View Prevalence (Last 30 Days):

   • Compute the view prevalence of Nudity in the last 30 days.
   • A post is defined as a Nudity post if violation_type = 'Nudity' and probability_violating > 0.5.
   • View prevalence is defined as: (total views on Nudity posts in the last 30 days) / (total views on all posts in the last 30 days).
   • Required output columns: nudity_views_30d, total_views_30d, nudity_view_prevalence_30d.

3. Compute Monthly Violation-Type Distribution:

   • For each calendar month derived from view_date:
     • Count the number of distinct posts viewed in that month that are classified as violating under the rule probability_violating > 0.5, grouped by violation_type.
     • Then, compute each violation_type's share of all violating posts viewed in that month.
   • Required output columns: month, violation_type, violating_post_count, monthly_violation_share.