Calculate Distinct High-View Posts and Spam View-Prevalence
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
content_views
| user_id | post_id | view_count | view_date |
| 101 | 572 | 3 | 2021-11-01 |
| 102 | 732 | 5 | 2021-11-02 |
| 103 | 153 | 12 | 2021-11-03 |
| 104 | 634 | 7 | 2021-11-01 |
violating_content
| post_id | violation_type | probability_violating |
| 572 | Spam | 0.70 |
| 732 | Scam | 0.85 |
| 153 | Nudity | 0.95 |
| 634 | Harassment | 0.50 |
##### Scenario
A social platform tracks harmful content and needs SQL reports for ops dashboards.
##### Question
Write SQL to return the count of distinct posts that accumulated more than 10 views within the past 7 days (inclusive). Write SQL to calculate the view-prevalence of Spam or Scam posts during the last 30 days (total Spam/Scam views ÷ total views).
##### Hints
Derive a rolling window from MAX(view_date); join violating_content; filter violation_type IN ('Spam','Scam'); aggregate as required.
Overview: This question evaluates proficiency in SQL-based data manipulation and analytical querying, including joins, aggregation, time-window filtering, and proportion calculations for operational metrics.
Using the tables content_views and violating_content, write a single SQL query that returns one row with two columns:
1) high_view_posts_count: the count of distinct posts that accumulated more than 10 total views between '2025-05-26' and '2025-06-01' (inclusive).
2) spam_scam_view_prevalence: the fraction (total views on Spam or Scam posts divided by total views on all posts) between '2025-05-03' and '2025-06-01' (inclusive), rounded to 4 decimal places.
Tables
content_views(user_id INTEGER, post_id INTEGER, view_count INTEGER, view_date DATE)
violating_content(post_id INTEGER, violation_type VARCHAR, probability_violating DECIMAL(4,2))
Hints
- For the 7-day metric, filter content_views where view_date is between '2025-05-26' and '2025-06-01' and sum view_count per post.
- Count posts whose summed view_count exceeds 10 to get high_view_posts_count.
Community answers
Answer by SS
With posts as( Select count(distinct post_id) as posts from ( Select post_id , sum(view_count) as total_Views from content_Views where view_date >= date '2025-05-31' and view_date <= date '2025-06-01' group by 1 having sum(view_count) > 10)),scam as( Select Sum(case when violation_type = 'Spam' OR violation_type = 'Scam' then view_count else 0 end) as spam_view, SUM(view_count) as total_Views from (Select a.user_id , a.post_id , a.view_count , a.view_date , b.violation_type from content_Views a left join violating_content b on a.post_id = b.post_id where a.view_date >= date '2025-05-31' and a.view_date <= date '2025-06-01'))
Select a.posts as high_view_posts_count , b.spam_view * 1.00/NULLIF(b.total_views,0) as spam_Scam_view_prevalancefrom posts a cross join scam b