Reported Posts II - Problem
You are given two tables:
Table: Actions
user_id(int): ID of the user who performed the actionpost_id(int): ID of the post the action was performed onaction_date(date): Date when the action was performedaction(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 -
actionis one of ('view', 'like', 'reaction', 'comment', 'report', 'share') -
extramay be null or contain additional information like 'spam', 'inappropriate', etc. - Each row in Removals represents a unique removed post
Visualization
Tap to expand
Understanding the Visualization
1
Filter
Get spam reports from Actions
2
Join
Match with Removals table
3
Calculate
Daily percentages and average
Key Takeaway
🎯 Key Insight: Use LEFT JOIN to preserve all spam reports, even those not removed, for accurate percentage calculation
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code