Count Posts With 10+ Views and Compute View-Weighted Spam/Scam Prevalence
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
A social media platform's integrity team tracks how often members see harmful content. Write one query that reports two numbers for the period covered by the tables:
1. **Posts with 10+ views:** the number of distinct posts whose total views are at least 10.
2. **Spam/scam prevalence:** the percentage of all views that landed on posts labeled `spam` or `scam`.
The report lists these two metrics without describing the tables or defining prevalence. This version fixes the schema below, totals a post's views across all of its daily rows, and defines prevalence as a view-weighted share: views on spam or scam posts divided by all views, rather than the share of posts that are spam or scam.
### Input Table(s)
**`post_daily_views`**: one row per post per day on which the post was viewed.
| Column | Type | Meaning |
| --- | --- | --- |
| `post_id` | INTEGER | The post |
| `view_date` | DATE | Calendar date |
| `views` | INTEGER | Number of views the post received on that date |
Primary key: (`post_id`, `view_date`).
**`post_labels`**: one row per harmful-content label applied to a post.
| Column | Type | Meaning |
| --- | --- | --- |
| `post_id` | INTEGER | The labeled post |
| `label` | TEXT | Harmful-content category, such as `spam`, `scam` or `hate_speech` |
Primary key: (`post_id`, `label`). A post with no harmful-content label has no rows here, and a post can carry several labels.
### Output Contract
Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns exactly one row with these columns, in this order:
| Column | Type | Meaning |
| --- | --- | --- |
| `posts_with_10_plus_views` | integer | Number of distinct posts whose `views`, summed over all of their rows, is at least 10 |
| `spam_scam_prevalence_pct` | numeric | 100 × (views on posts that have a `spam` or `scam` label) ÷ (all views in `post_daily_views`), rounded to 2 decimal places |
The result always has exactly one row, so no `ORDER BY` is needed. `posts_with_10_plus_views` is `0`, never NULL, when no post qualifies.
### Example
`post_daily_views`:
| post_id | view_date | views |
| --- | --- | --- |
| 1 | 2025-06-01 | 6 |
| 1 | 2025-06-02 | 5 |
| 2 | 2025-06-01 | 10 |
| 3 | 2025-06-01 | 4 |
| 3 | 2025-06-03 | 5 |
| 4 | 2025-06-02 | 25 |
| 5 | 2025-06-01 | 20 |
| 5 | 2025-06-02 | 20 |
| 6 | 2025-06-03 | 2 |
`post_labels`:
| post_id | label |
| --- | --- |
| 1 | scam |
| 1 | spam |
| 3 | hate_speech |
| 4 | scam |
| 6 | spam |
| 7 | spam |
Expected result:
| posts_with_10_plus_views | spam_scam_prevalence_pct |
| --- | --- |
| 4 | 39.18 |
- Total views per post: post 1 has 11, post 2 has 10, post 3 has 9, post 4 has 25, post 5 has 40 and post 6 has 2, for 97 views in all. Posts 1, 2, 4 and 5 reach at least 10 views. Post 1 never has 10 views on a single day, and post 2 has exactly 10.
- Posts 1, 4 and 6 carry a `spam` or `scam` label and received 11 + 25 + 2 = 38 views. Post 1 has both labels, but its views count once. Post 3's `hate_speech` label does not count, and post 7 has no views. Prevalence is 100 × 38 ÷ 97 = 39.175…, which rounds to 39.18.
### Constraints and Clarifications
- No column contains NULL, and `views` is a positive integer.
- `post_daily_views` has at least one row, so the total number of views is positive.
- Views are counted as recorded in `views`; repeat views by the same member are already included and are not deduplicated.
- The 10-view threshold applies to a post's total over all of its rows, not to any single day, and it is inclusive.
- Labels are compared exactly as stored, and only `spam` and `scam` count toward prevalence. A post's views count once even if it has both labels.
- Labeled posts with no rows in `post_daily_views` contribute no views. The tables cover one fixed period, so do not filter by date.
- Compute the percentage with exact numeric arithmetic rather than integer division, and round only the final value.
```hint Watch the join grain
Before attaching labels to views, ask how many label rows a single post can match.
```
Overview: A SQL exercise on harmful-content data: count the posts whose daily views add up to at least 10, and compute spam and scam prevalence as the share of all views that land on posts with those labels. It tests aggregating before applying a threshold, avoiding double counting when a post carries several labels, and exact percentage arithmetic.