Reported Posts II

Reported Posts II

What is this problem about?

The Reported Posts II interview question is a more advanced version of the Reported Posts problem. Given a Reports table and a Removals table (tracking which reported posts were actually removed by moderators), calculate the average daily percentage of posts that were reported as "spam" and subsequently removed, rounded to 2 decimal places. This involves multi-table joins, date-wise aggregation, and averaging percentages.

Why is this asked in interviews?

Meta asks this MEDIUM database problem because it tests multi-step SQL: joining tables, grouping by date, computing per-day percentages, and then averaging those percentages. It mirrors real analytics workflows at content platforms where moderation efficiency is tracked daily. It evaluates whether candidates can chain CTEs or subqueries cleanly to express complex multi-step aggregations.

Algorithmic pattern used

The pattern is CTE-based multi-step aggregation. Step 1: join Reports (filtered for spam) with Removals on post_id to find daily removed spam counts and total daily unique spam reports. Step 2: compute the per-day removal percentage. Step 3: average those daily percentages. Using a CTE:

WITH daily AS (
  SELECT r.action_date,
         COUNT(DISTINCT r.post_id) AS reported,
         COUNT(DISTINCT rm.post_id) AS removed
  FROM Reports r
  LEFT JOIN Removals rm ON r.post_id = rm.post_id
  WHERE r.action_reason = 'spam'
  GROUP BY r.action_date
)
SELECT ROUND(AVG(removed * 100.0 / reported), 2) AS average_daily_percent
FROM daily;

Example explanation

Day 1: 3 spam posts reported, 1 removed → 33.33%. Day 2: 2 spam posts reported, 2 removed → 100%.

Average daily percentage: (33.33 + 100) / 2 = 66.67%.

Note: you average the per-day percentages, NOT the overall total. This distinction matters when different days have different report volumes.

Common mistakes candidates make

• Computing the overall percentage across all days instead of averaging per-day percentages.
• Using INNER JOIN instead of LEFT JOIN with Removals, excluding days with 0 removals.
• Forgetting to filter for only 'spam' action_reason in the Reports table.
• Not using COUNT(DISTINCT post_id) to avoid double-counting duplicate reports.

Interview preparation tip

For the Reported Posts II coding problem, the database interview pattern requires multi-step aggregation using CTEs. The key conceptual trap is "average of daily percentages" vs "percentage of totals" — know the difference and explain it. Meta interviewers value candidates who use CTEs for readability over deeply nested subqueries. Practice CTE-based SQL patterns for multi-step analytics — they appear frequently in data science and data engineering interviews.