Skip to main content
Talentd

Reported Posts - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min readAsked at: Meta
Practice this problem

Problem Statement

Table: Actions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| post_id       | int     |
| action_date   | date    | 
| action        | enum    |
| extra         | varchar |
+---------------+---------+
This table may have duplicate rows.
The action column is an ENUM (category) type of ('view', 'like', 'reaction', 'comment', 'report', 'share').
The extra column has optional information about the action, such as a reason for the report or a type of reaction.

 

Write a solution to report the number of posts reported yesterday for each report reason. Assume today is 2019-07-05.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Actions table:
+---------+---------+-------------+--------+--------+
| 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       | 4       | 2019-07-04  | view   | null   |
| 2       | 4       | 2019-07-04  | report | spam   |
| 3       | 4       | 2019-07-04  | view   | null   |
| 3       | 4       | 2019-07-04  | report | spam   |
| 4       | 3       | 2019-07-02  | view   | null   |
| 4       | 3       | 2019-07-02  | report | spam   |
| 5       | 2       | 2019-07-04  | view   | null   |
| 5       | 2       | 2019-07-04  | report | racism |
| 5       | 5       | 2019-07-04  | view   | null   |
| 5       | 5       | 2019-07-04  | report | racism |
+---------+---------+-------------+--------+--------+
Output: 
+---------------+--------------+
| report_reason | report_count |
+---------------+--------------+
| spam          | 1            |
| racism        | 2            |
+---------------+--------------+
Explanation: Note that we only care about report reasons with non-zero number of reports.

Approach Overview

Problem Overview: The Actions table logs user interactions with posts. Some rows represent reports where action = 'report' and the extra column stores the report reason (spam, racism, etc.). The task is to compute how many unique posts were reported yesterday for each reason.

Approach 1: Filter + GROUP BY with DISTINCT (O(n) time, O(k) space)

Scan the Actions table and keep only rows where action = 'report' and action_date equals yesterday’s date. Each row represents a report event, but a single post may be reported multiple times for the same reason. Because the problem asks for reported posts, count unique post_id values using COUNT(DISTINCT post_id). Group the filtered rows by the reason stored in extra. The result gives one row per report reason with the number of distinct posts reported for that reason. The query performs a single table scan, making it O(n) time with O(k) space for grouped results, where k is the number of distinct report reasons.

This approach relies on SQL aggregation primitives from SQL and database querying. The key operations are filtering with WHERE, grouping with GROUP BY, and deduplicating posts with COUNT(DISTINCT ...). Databases execute this efficiently using hash aggregation or sorting internally.

Approach 2: Subquery for Distinct Reports then Aggregate (O(n) time, O(n) space)

Another option is to first extract distinct combinations of (post_id, extra) in a subquery after applying the same filters. This step removes duplicate report events early. The outer query then groups the result by extra and counts rows. While logically equivalent, it materializes an intermediate dataset and can use more memory depending on the engine’s execution plan. Time complexity remains O(n) because the table still needs to be scanned.

This variation highlights how deduplication and aggregation can be separated into stages using nested queries. It’s useful when you want explicit control over intermediate transformations or when building more complex reporting pipelines involving GROUP BY operations.

Recommended for interviews: The direct GROUP BY solution with COUNT(DISTINCT post_id). It shows you understand filtering, aggregation, and deduplication in SQL with a single concise query. The subquery version demonstrates the same logic but is typically less direct.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Filter + GROUP BY with COUNT(DISTINCT)O(n)O(k)Best general solution; concise and optimized for SQL aggregation
Subquery with DISTINCT then GROUP BYO(n)O(n)Useful when deduplication must be explicit before aggregation

Video Solution

LeetCode 1113. Reported Posts • Yuki Kitayama • 45 views views

Frequently Asked Questions

Is Reported Posts easy or hard?
Reported Posts is classified as an Easy database problem. It focuses on basic SQL concepts like filtering with WHERE, grouping with GROUP BY, and deduplicating records with COUNT(DISTINCT).
Reported Posts Python/Java solution
This problem is a database question, so the solution is written in SQL rather than Python or Java. The query filters report actions, groups by the reason, and counts distinct post IDs using COUNT(DISTINCT post_id).
How to solve Reported Posts in O(n)?
Filter rows where action = 'report' and action_date is yesterday. Then group the results by the extra column and compute COUNT(DISTINCT post_id). This uses SQL aggregation and performs a single pass over the table, resulting in O(n) time complexity.
What is the best approach for Reported Posts?
The most efficient approach filters the Actions table for rows where action = 'report' and action_date equals yesterday, then groups by the report reason (extra). Using COUNT(DISTINCT post_id) ensures each reported post is counted once per reason. The query runs in O(n) time with a single scan of the table.
Is Reported Posts asked at Google/Amazon/Meta?
Reported Posts represents a common SQL aggregation pattern used in real interview questions at companies like Amazon and Meta. It tests understanding of filtering, GROUP BY, and counting distinct records in relational databases.
What data structure is used in Reported Posts?
The solution operates on a relational database table and relies on SQL aggregation. Internally, database engines may use hash tables or sorting structures to implement GROUP BY and COUNT(DISTINCT) operations efficiently.
What is the time complexity of Reported Posts?
The SQL solution typically runs in O(n) time because the database scans the Actions table once to filter report rows and perform aggregation. Space complexity is O(k), where k is the number of distinct report reasons stored in the extra column.

Ready to solve this problem?

Practice Reported Posts with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary