Compute Moderation View Metrics

A content-moderation analytics team stores its viewing and policy data in a warehouse with two tables. Write a single SQL query for each of the three questions listed at the end.

content_views tracks attention on posts. Each row captures what one viewer did with one post on one calendar day, and the combination (user_id, post_id, view_date) is its primary key. The columns are user_id (the viewing user), post_id (the post that was viewed), view_count (how many views that user produced for that post on that day), and view_date (the day, held as a string in YYYY-MM-DD form).

post_policy_scores holds the moderation model's output for each post under each policy category, keyed on (post_id, violation_type). Its columns are post_id, violation_type (a category label such as Spam, Scam, Nudity, or Harassment), and probability_violating, a double giving the model's estimated likelihood that the post breaches that policy. The same post can occupy several rows here, one per policy category it was scored against. The two tables are linked by post_id.

Apply the following conventions:

• Because view_date is a UTC day string, cast it to DATE whenever you compare or group on it.
• Let analysis_date be the largest view_date appearing in content_views.
• Both the trailing 7-day and trailing 30-day windows are inclusive and both terminate on analysis_date.
• For any question that involves classifying posts, count a post as belonging to a violation type when its probability_violating value for that type is greater than 0.5.
• When you aggregate by month, derive the calendar month from view_date.

Questions

• Q1 — Which posts collected at least 10 views inside the trailing 7-day window?
• Q2 — How prevalent were posts classified as Nudity among views inside the trailing 30-day window?
• Q3 — How do the violation types break down month by month?

Constraints:

• (user_id, post_id, view_date) is the primary key of content_views.
• (post_id, violation_type) is the primary key of post_policy_scores.
• A single post may have multiple rows in post_policy_scores, one for each possible violation type.
• view_date is stored as a string formatted YYYY-MM-DD and represents a UTC date.
• probability_violating is a double in the range from 0 to 1.

Examples

Example

Input:
INSERT INTO content_views (user_id, post_id, view_count, view_date) VALUES
  (1, 101, 6, '2024-10-30'),
  (2, 101, 5, '2024-10-31'),
  (3, 102, 12, '2024-10-31'),
  (4, 103, 3, '2024-09-15'),
  (5, 104, 8, '2024-10-05'),
  (6, 105, 7, '2024-10-31'),
  (7, 106, 4, '2024-10-20'),
  (8, 108, 4, '2024-10-25'),
  (9, 108, 6, '2024-10-24'),
  (10, 109, 5, '2024-10-02'),
  (11, 109, 100, '2024-10-01');

INSERT INTO post_policy_scores (post_id, violation_type, probability_violating) VALUES
  (101, 'Nudity', 0.72),
  (101, 'Spam', 0.20),
  (102, 'Scam', 0.61),
  (102, 'Nudity', 0.10),
  (103, 'Scam', 0.55),
  (103, 'Spam', 0.10),
  (104, 'Nudity', 0.89),
  (104, 'Harassment', 0.05),
  (105, 'Harassment', 0.55),
  (106, 'Nudity', 0.50),
  (106, 'Spam', 0.51),
  (107, 'Scam', 0.90),
  (108, 'Nudity', NULL),
  (109, 'Harassment', 0.80);
Output:
section | month | violation_type | post_id | metric
Q1 | NULL | NULL | 101 | NULL
Q1 | NULL | NULL | 102 | NULL
Q2 | NULL | NULL | NULL | 0.33333333333333333333
Q3 | 2024-09 | Scam | NULL | 1
Q3 | 2024-10 | Harassment | NULL | 2
Q3 | 2024-10 | Nudity | NULL | 2
Q3 | 2024-10 | Scam | NULL | 1
Q3 | 2024-10 | Spam | NULL | 1