1132.reported-posts-II.md

1132. Reported Posts II

Solution 1: CTE w/ Subquery

reported: A temp table which contains the rows with unique spam reported on a given day.
daily_percent: A cte which contains the average removal percentage for each day.
If the spam reported post exists in the removals table, count 1, otherwise 0. Get the average of each daily percentage.

Using the daily_percent table, get the average of each daily percentage.

Runtime: 962ms

WITH daily_percent AS (
  SELECT 
  AVG(IF(reported.post_id IN(
    SELECT post_id
    FROM Removals
  ), 1, 0)) * 100
  AS daily_percent
  FROM (
    SELECT DISTINCT post_id, action_date
    FROM Actions 
    WHERE extra = 'spam'
  ) AS reported
  GROUP BY reported.action_date
)

SELECT ROUND(AVG(daily_percent), 2) AS average_daily_percent
FROM daily_percent;

Solution 2: CTE w/ Left Join

CTE vs Subquery: CTEs are more readable, and performance-wise they are similar.

daily_percent: A cte which contains the average removal percentage for each day. This time we use left join instead of a subquery.
The next part is the same as solution 1.

Runtime: 814ms

WITH daily_percent AS (
  SELECT COUNT(DISTINCT Removals.post_id) / COUNT(DISTINCT actions.post_id) * 100 AS daily_percent
  FROM actions
  LEFT JOIN Removals USING(post_id)
  WHERE actions.extra = 'spam'
  GROUP BY actions.action_date
)

SELECT ROUND(AVG(daily_percent), 2) AS average_daily_percent
FROM daily_percent;