Reported Posts - Solution & Explanation

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.