Calculate Daily Harmful Content View Percentage
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
VIDEOS
+----+-------------+------------+
| id | uploader_id | is_harmful |
+----+-------------+------------+
| 1 | 101 | true |
| 2 | 102 | false |
| 3 | 101 | true |
+----+-------------+------------+
VIEWS
+----------+---------+---------------------+
| video_id | user_id | view_time |
+----------+---------+---------------------+
| 1 | 1001 | 2023-07-01 10:00:00 |
| 2 | 1002 | 2023-07-01 10:01:00 |
| 1 | 1003 | 2023-07-01 10:02:00 |
+----------+---------+---------------------+
##### Scenario
A video-sharing platform wants to quantify how often users watch harmful content.
##### Question
Using the provided tables, write a SQL query that returns daily view_prevalence – the percentage of all views in a day that were on videos flagged as harmful.
##### Hints
Join videos to views; use SUM(CASE…) / COUNT(*) grouped by DATE(view_time).
Overview: This question evaluates proficiency in data manipulation and aggregation for computing event-based metrics, testing competency in SQL/Python data processing within the Data Manipulation (SQL/Python) domain.
Using the VIDEOS and VIEWS tables, write a SQL query that returns daily view_prevalence – the percentage of all views in a given calendar day that were on videos flagged as harmful (is_harmful = true). Return one row per day with the date and the harmful view percentage, rounded to two decimal places.
Tables
VIDEOS(id INTEGER, uploader_id INTEGER, is_harmful BOOLEAN)
VIEWS(video_id INTEGER, user_id INTEGER, view_time TIMESTAMP)
Hints
- Join VIDEOS to VIEWS on video_id = id.
- Compute SUM(CASE WHEN is_harmful THEN 1 ELSE 0 END) / COUNT(*) per day.
Community answers
Answer by SS
With total as( Select a.video_id , a.user_id , a.view_Time , b.is_harmful from views a Left join Videos b on a.video_id = b.id )
Select date(view_time) ,Sum(case when is_harmful = true then 1 else 0 end) 100.00/NULLIF(count(),0)from total group by 1