Reported Posts - Practice Coding Problems

Reported Posts - Problem

Database Easy

You're building a content moderation system for a social media platform. The platform tracks all user interactions with posts in an Actions table, including views, likes, comments, and most importantly - reports.

Your mission: Generate a daily report showing how many posts were reported yesterday, grouped by the reason for reporting. This helps the moderation team understand what types of content issues are most common.

Table: Actions

Column Name	Type
user_id	int
post_id	int
action_date	date
action	enum
extra	varchar

The action column contains values like 'view', 'like', 'reaction', 'comment', 'report', 'share'. When action = 'report', the extra column contains the reason for the report (e.g., 'spam', 'violence', 'hate speech').

Given: Today is 2019-07-05

Return: Number of posts reported on 2019-07-04, grouped by report reason, in any order.

Input & Output

example_1.sql — Basic Report Counting

$ Input: Actions table: | user_id | post_id | action_date | action | extra | |---------|---------|-------------|--------|----------| | 1 | 1 | 2019-07-04 | view | null | | 1 | 1 | 2019-07-04 | report | spam | | 1 | 1 | 2019-07-05 | view | null | | 2 | 2 | 2019-07-04 | report | spam | | 2 | 2 | 2019-07-04 | report | violence | | 3 | 4 | 2019-07-04 | view | null |

› Output: | report_reason | report_count | |---------------|---------------| | spam | 2 | | violence | 1 |

💡 Note: On 2019-07-04 (yesterday), there were 2 posts reported for 'spam' (post_id 1 and 2) and 1 post reported for 'violence' (post_id 2). Note that post_id 2 was reported for both reasons, so it appears in both counts.

example_2.sql — No Reports Yesterday

$ Input: Actions table: | user_id | post_id | action_date | action | extra | |---------|---------|-------------|--------|---------| | 1 | 1 | 2019-07-03 | report | spam | | 1 | 1 | 2019-07-05 | report | spam | | 2 | 2 | 2019-07-04 | view | null |

› Output: Empty result set: | report_reason | report_count | |---------------|---------------|

💡 Note: No posts were reported on 2019-07-04 (yesterday). The reports happened on 2019-07-03 and 2019-07-05, so our result is empty.

example_3.sql — Multiple Reports Same Post

$ Input: Actions table: | user_id | post_id | action_date | action | extra | |---------|---------|-------------|--------|-----------| | 1 | 1 | 2019-07-04 | report | spam | | 2 | 1 | 2019-07-04 | report | spam | | 3 | 1 | 2019-07-04 | report | violence | | 4 | 2 | 2019-07-04 | report | spam |

› Output: | report_reason | report_count | |---------------|---------------| | spam | 2 | | violence | 1 |

💡 Note: Post 1 was reported by 3 different users (2 for spam, 1 for violence), but we count distinct posts, so 'spam' gets count 2 (posts 1 and 2) and 'violence' gets count 1 (post 1). Multiple reports of the same post for the same reason don't inflate the count.

Visualization

Tap to expand

Understanding the Visualization

User Reports

Throughout the day, users report posts for various reasons (spam, violence, hate speech, etc.)

Data Collection

All reports are logged in the Actions table with timestamps and reasons

Daily Filter

At the end of each day, we filter for yesterday's reports only

Smart Grouping

Group reports by reason and count unique posts (not duplicate reports)

Moderation Report

Present clean summary to moderation team for prioritization

Key Takeaway

🎯 Key Insight: Database aggregation functions like GROUP BY and COUNT(DISTINCT) are specifically designed for this type of analysis. Let the database do what it does best - filtering, grouping, and counting large datasets efficiently!

Time & Space Complexity

Time Complexity

⏱️

O(n)

Linear scan with database optimizations, potentially much faster with indexes on action_date and action columns

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique report reasons for grouping results

✓ Linear Space

Constraints

The Actions table may contain duplicate rows
action is an ENUM with values: 'view', 'like', 'reaction', 'comment', 'report', 'share'
extra column is optional and contains additional information like report reasons
Today's date is fixed as 2019-07-05, so yesterday is 2019-07-04
Return results in any order
If no reports found for yesterday, return empty result set

Asked in

f Meta 45 G Google 38 a Amazon 32 ⊞ Microsoft 28

This is a classic SQL aggregation problem solved optimally with WHERE filtering, GROUP BY, and COUNT(DISTINCT). The key insight is using COUNT(DISTINCT post_id) to ensure each post is counted only once per reason, even if reported multiple times. The database engine handles all optimization automatically.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Manual Filtering and Counting)	O(n)	O(k)	Scan every record manually and count reports by reason
Single SQL Query (Optimal)	O(n)	O(k)	Use SQL's WHERE, GROUP BY, and COUNT to get results in one efficient query

Brute Force (Manual Filtering and Counting) — Algorithm Steps

Fetch all action records from the table
Filter records where action = 'report' and action_date = yesterday
Manually iterate through filtered records
Count occurrences of each report reason
Format results as required

Visualization

Tap to expand

Step-by-Step Walkthrough

Start Scanning

Begin examining each record from the first row

Check Date & Action

For each record, verify if it's a report from yesterday

Count Manually

If it matches, manually increment the counter for that reason

Continue Until End

Repeat for every single record in the table

Code -

solution.c — C

// Simulating brute force approach with C
#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#define MAX_RECORDS 10000
#define MAX_REASONS 100

typedef struct {
    int user_id, post_id;
    char action_date[11];
    char action[10];
    char extra[100];
} ActionRecord;

typedef struct {
    char reason[100];
    int count;
} ReportResult;

int bruteForceSolution(ActionRecord* actions, int n, ReportResult* results) {
    char yesterday[] = "2019-07-04";
    int resultCount = 0;
    
    // Brute force: process every record
    for (int i = 0; i < n; i++) {
        if (strcmp(actions[i].action_date, yesterday) == 0 && 
            strcmp(actions[i].action, "report") == 0) {
            
            char* reason = strlen(actions[i].extra) > 0 ? actions[i].extra : "No reason";
            
            // Find existing reason or create new
            int found = 0;
            for (int j = 0; j < resultCount; j++) {
                if (strcmp(results[j].reason, reason) == 0) {
                    results[j].count++;
                    found = 1;
                    break;
                }
            }
            
            if (!found) {
                strcpy(results[resultCount].reason, reason);
                results[resultCount].count = 1;
                resultCount++;
            }
        }
    }
    
    return resultCount;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Must scan through all n records in the table to find yesterday's reports

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique report reasons (for counting)

✓ Linear Space

Constraints

The Actions table may contain duplicate rows
action is an ENUM with values: 'view', 'like', 'reaction', 'comment', 'report', 'share'
extra column is optional and contains additional information like report reasons
Today's date is fixed as 2019-07-05, so yesterday is 2019-07-04
Return results in any order
If no reports found for yesterday, return empty result set

23.4K Views

High Frequency

~12 min Avg. Time

856 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

Brute Force (Manual Filtering and Counting) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler