Calculate Daily Harmful Content View Percentage

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).

Daily Harmful Content View Prevalence

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.

Table VIDEOS

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
uploader_id	INTEGER	NOT NULL
is_harmful	BOOLEAN	NOT NULL

Sample data:

id	is_harmful	uploader_id
1	true	101
2	false	102
3	true	101

Table VIEWS

column	type	constraints
video_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES VIDEOS(id)
user_id	INTEGER	NOT NULL
view_time	TIMESTAMP	NOT NULL

Sample data:

user_id	video_id	view_time
1001	1	2023-07-01 10:00:00
1002	2	2023-07-01 10:01:00
1003	1	2023-07-01 10:02:00

Expected output

For each calendar date, return the date and the percentage of views that were on harmful videos (is_harmful = true). Percentage is rounded to two decimals.

view_date	view_prevalence
2023-07-01	66.67

Hints

1. Join VIDEOS to VIEWS on video_id = id.
2. Compute SUM(CASE WHEN is_harmful THEN 1 ELSE 0 END) / COUNT(*) per day.
3. Group by DATE(view_time).