Reported Posts Analysis
ASKED IN INTERVIEWMeta
Problem Statement
Meta's integrity team wants to measure the accuracy of spam reports. You need to find the total number of unique posts that were reported for the reason spam and were later deleted (present in the removals table).
Rules:
- Only consider reports where
extrais 'spam'. - A post is considered "deleted" if its
post_idexists in theremovalstable. - Output column:
spam_delete_count.
Tests your understanding of
Joins, Filtering and Aggregation
Input Tables
actions
| user_id(INTEGER) | post_id(INTEGER) | action_date(DATE) | action(VARCHAR) | extra(VARCHAR) |
|---|---|---|---|---|
| 1 | 101 | 2026-02-01 | report | spam |
| 2 | 101 | 2026-02-01 | report | spam |
| 3 | 102 | 2026-02-02 | report | racism |
| 4 | 103 | 2026-02-02 | report | spam |
removals
| post_id(INTEGER) | remove_date(DATE) |
|---|---|
| 101 | 2026-02-03 |
| 102 | 2026-02-03 |
post_metadata
| post_id(INTEGER) | author_id(INTEGER) | post_type(VARCHAR) |
|---|---|---|
| 101 | 99 | ad |
| 103 | 88 | status |
Expected Output
| spam_delete_count(INTEGER) |
|---|
| 1 |
Tags
EasyASKED IN INTERVIEWJoinsFilteringAggregation
10-15 min
72%