Reported Posts II - Problem

You are given two tables:

Table: Actions

  • user_id (int): ID of the user who performed the action
  • post_id (int): ID of the post the action was performed on
  • action_date (date): Date when the action was performed
  • action (enum): Type of action ('view', 'like', 'reaction', 'comment', 'report', 'share')
  • extra (varchar): Additional information about the action (e.g., reason for report)

Table: Removals

  • post_id (int): ID of the removed post (primary key)
  • remove_date (date): Date when the post was removed

Write a SQL query to find the average daily percentage of posts that got removed after being reported as spam. The result should be rounded to 2 decimal places.

Table Schema

Actions
Column Name Type Description
user_id int ID of the user performing the action
post_id int ID of the post being acted upon
action_date date Date when the action was performed
action varchar Type of action (view, like, reaction, comment, report, share)
extra varchar Additional information like report reason
Primary Key: No primary key (may have duplicates)
Removals
Column Name Type Description
post_id PK int ID of the removed post
remove_date date Date when the post was removed
Primary Key: post_id

Input & Output

Example 1 — Basic Spam Report Analysis
Input Tables:
Actions
user_id post_id action_date action extra
1 1 2019-07-04 view
1 1 2019-07-04 report spam
1 2 2019-07-04 report spam
2 4 2019-07-05 report spam
Removals
post_id remove_date
2 2019-07-20
Output:
average_daily_percent
25
💡 Note:

On 2019-07-04: 2 spam reports (posts 1,2), 1 removal (post 2) = 50%. On 2019-07-05: 1 spam report (post 4), 0 removals = 0%. Average: (50 + 0) / 2 = 25.00%
Example 2 — All Reports Removed
Input Tables:
Actions
user_id post_id action_date action extra
1 1 2019-07-04 report spam
2 2 2019-07-04 report spam
Removals
post_id remove_date
1 2019-07-05
2 2019-07-06
Output:
average_daily_percent
100
💡 Note:

On 2019-07-04: 2 spam reports (posts 1,2), both were removed = 2/2 = 100%. Only one day with reports, so average is 100.00%

Constraints

  • 1 ≤ user_id, post_id ≤ 10000
  • action is one of ('view', 'like', 'reaction', 'comment', 'report', 'share')
  • extra may be null or contain additional information like 'spam', 'inappropriate', etc.
  • Each row in Removals represents a unique removed post

Visualization

Tap to expand
Reported Posts II - Solution INPUT TABLES Actions Table: user_id | post_id | action_date action | extra 1 | 1 | 2019-07-01 | report | spam 1 | 2 | 2019-07-02 | report | spam 1 | 3 | 2019-07-02 | report | spam 2 | 4 | 2019-07-03 | report | spam Removals Table: post_id | remove_date 1 | 2019-07-02 2 | 2019-07-03 3 | 2019-07-03 Find spam reports and their removals ALGORITHM STEPS 1 Filter Spam Reports WHERE action='report' AND extra='spam' 2 LEFT JOIN Removals Match spam posts with removal records 3 Calculate Daily % removed / reported * 100 GROUP BY action_date 4 Average All Days AVG(daily_percent) ROUND to 2 decimals Day 1: 1/1 = 100% Day 2: 2/2 = 100% Day 3: 0/1 = 0% AVG = (100+100+0)/3 FINAL RESULT Average Daily Removal % 66.67 average_daily_percent Calculation Breakdown: 2019-07-01: 1 spam, 1 removed --> 100% 2019-07-02: 2 spam, 2 removed --> 100% 2019-07-03: 1 spam, 0 removed --> 0% AVG: (100+100+0)/3 = 66.67 OK - SOLVED Key Insight: The key is to first calculate the removal percentage for EACH DAY separately, then take the average of those daily percentages. This is different from calculating the overall percentage of all removed posts. Using LEFT JOIN ensures we include spam reports even when the post wasn't removed (NULL in Removals). COUNT(r.post_id) counts only matched removals, not NULLs. TutorialsPoint - Reported Posts II | Optimal Solution

Related Problems

Asked in
Facebook 28 Twitter 15 Instagram 12
32.4K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen