Coding questions · Meta
Calculate Distinct High-View Posts and Spam View-Prevalence
This question evaluates proficiency in SQL-based data manipulation and analytical querying, including joins, aggregation, time-window filtering, and proportion calculations for operational metrics.
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.
High-View Posts and Spam/Scam View Prevalence
Using the tables content_views and violating_content, write a single SQL query that returns one row with two columns:
- 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).
- 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.
Table content_views
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | |
| post_id | INTEGER | |
| view_count | INTEGER | |
| view_date | DATE |
Sample data:
| post_id | user_id | view_count | view_date |
|---|---|---|---|
| 572 | 101 | 3 | 2025-05-30 |
| 732 | 102 | 5 | 2025-05-31 |
| 153 | 103 | 12 | 2025-06-01 |
| 634 | 104 | 7 | 2025-05-30 |
Table violating_content
| column | type | constraints |
|---|---|---|
| post_id | INTEGER | |
| violation_type | VARCHAR | |
| probability_violating | DECIMAL(4,2) |
Sample data:
| post_id | probability_violating | violation_type |
|---|---|---|
| 572 | 0.7 | Spam |
| 732 | 0.85 | Scam |
| 153 | 0.95 | Nudity |
| 634 | 0.5 | Harassment |
Expected output
One row with two metrics: (1) the number of distinct posts whose total views exceed 10 between '2025-05-26' and '2025-06-01'; and (2) the Spam/Scam view-prevalence (total views on Spam/Scam posts divided by total views on all posts) between '2025-05-03' and '2025-06-01', rounded to 4 decimals.
| high_view_posts_count | spam_scam_view_prevalence |
|---|---|
| 1 | 0.2963 |
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.
- For the prevalence metric, restrict to view_date between '2025-05-03' and '2025-06-01'.
- Join violating_content and filter violation_type IN ('Spam','Scam') for the numerator, then divide by total views and round to 4 decimals.