Reported Posts II - Practice Coding Problems

Reported Posts II - Problem

Database Medium

You're working as a content moderator for a social media platform, analyzing the effectiveness of your spam detection system. Your goal is to calculate the daily removal rate of posts that were reported as spam.

You have access to two tables:

Actions: Records all user interactions with posts, including reports
Removals: Records when posts were actually removed by moderators

Your task is to find the average daily percentage of spam-reported posts that got removed, rounded to 2 decimal places.

Key Challenge: You need to match posts that were both reported as spam AND subsequently removed, then calculate the daily removal rate across all days.

Think of it like measuring how effective your content moderation team is at acting on spam reports!

Input & Output

basic_example.sql — Basic Case

$ Input: Actions: | user_id | post_id | action_date | action | extra | |---------|---------|-------------|--------|---------| | 1 | 1 | 2019-07-01 | view | null | | 1 | 1 | 2019-07-01 | like | null | | 1 | 1 | 2019-07-01 | share | null | | 2 | 2 | 2019-07-01 | report | spam | | 2 | 3 | 2019-07-02 | report | spam | Removals: | post_id | remove_date | |---------|-------------| | 2 | 2019-07-02 |

› Output: | average_daily_percent | |-----------------------| | 50.00 |

💡 Note: Day 1: 1 spam report (post_id=2), 1 removed → 100%. Day 2: 1 spam report (post_id=3), 0 removed → 0%. Average = (100 + 0) / 2 = 50.00%

no_removals.sql — No Spam Removals

$ Input: Actions: | user_id | post_id | action_date | action | extra | |---------|---------|-------------|--------|---------| | 1 | 1 | 2019-07-01 | report | spam | | 2 | 2 | 2019-07-01 | report | spam | Removals: | post_id | remove_date | |---------|-------------|

› Output: | average_daily_percent | |-----------------------| | 0.00 |

💡 Note: 2 posts reported as spam on 2019-07-01, but none were removed. Daily percentage = 0/2 * 100 = 0.00%

all_removed.sql — All Spam Removed

$ Input: Actions: | user_id | post_id | action_date | action | extra | |---------|---------|-------------|--------|---------| | 1 | 1 | 2019-07-01 | report | spam | | 2 | 2 | 2019-07-01 | report | spam | | 3 | 3 | 2019-07-02 | report | spam | Removals: | post_id | remove_date | |---------|-------------| | 1 | 2019-07-02 | | 2 | 2019-07-03 | | 3 | 2019-07-02 |

› Output: | average_daily_percent | |-----------------------| | 100.00 |

💡 Note: Day 1: 2 reports, both removed → 100%. Day 2: 1 report, removed → 100%. Average = (100 + 100) / 2 = 100.00%

Visualization

Tap to expand

Understanding the Visualization

Daily Spam Reports

Collect all unique posts reported as spam each day

Match with Removals

Check which reported posts were actually removed

Calculate Daily Rate

For each day: (removed posts / reported posts) × 100

Average Performance

Average all daily percentages to get overall effectiveness

Key Takeaway

🎯 Key Insight: Group by date first to calculate daily percentages correctly, then average those percentages. This approach handles varying daily volumes and provides meaningful effectiveness metrics.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan of Actions table plus efficient JOIN operation with Removals table

⚡ Linearithmic

Space Complexity

O(n)

Space needed for intermediate CTE results and JOIN operation

⚡ Linearithmic Space

Constraints

1 ≤ user_id, post_id ≤ 10⁵
action_date and remove_date are valid dates
action ∈ {'view', 'like', 'reaction', 'comment', 'report', 'share'}
extra field is 'spam' only for report actions we care about
A post can be reported multiple times but should be counted once per day

Asked in

f Meta 45 G Google 38 t Twitter 32 in LinkedIn 28

The optimal solution uses a Common Table Expression (CTE) to first identify distinct spam reports by date, then performs an efficient LEFT JOIN with the Removals table. Key insight: group by action_date first to calculate daily percentages, then take the average across all days. This approach handles edge cases like multiple reports of the same post and ensures accurate percentage calculation.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries Approach	O(n²)	O(1)	Use nested subqueries to check each reported post individually
Optimized JOIN with CTE (Optimal)	O(n log n)	O(n)	Use Common Table Expression with efficient JOIN to process data in one pass

Nested Subqueries Approach — Algorithm Steps

Find all posts reported as spam with their action dates
For each reported post, check if it exists in the removals table
Calculate daily percentages using nested aggregations
Average the daily percentages and round to 2 decimals

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter Spam Reports

Find all posts reported as spam

Check Each Removal

For each report, search through removals table

Calculate Daily Rates

Group by date and compute percentages

Average Results

Take average of all daily percentages

Code -

solution.c — C

// SQL solution for database query
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = """
    SELECT ROUND(AVG(daily_percent), 2) as average_daily_percent
    FROM (
        SELECT 
            action_date,
            CASE 
                WHEN COUNT(*) = 0 THEN 0
                ELSE 100.0 * SUM(CASE WHEN removed.post_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*)
            END as daily_percent
        FROM (
            SELECT DISTINCT post_id, action_date
            FROM Actions 
            WHERE action = 'report' AND extra = 'spam'
        ) reports
        LEFT JOIN Removals removed ON reports.post_id = removed.post_id
        GROUP BY action_date
    ) daily_stats;
    """;
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Nested queries result in quadratic complexity as each reported post is checked against all removals

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond query processing

✓ Linear Space

Constraints

1 ≤ user_id, post_id ≤ 10⁵
action_date and remove_date are valid dates
action ∈ {'view', 'like', 'reaction', 'comment', 'report', 'share'}
extra field is 'spam' only for report actions we care about
A post can be reported multiple times but should be counted once per day

28.4K Views

High Frequency

~25 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Nested Subqueries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler