LeetCode 1132. Reported Posts II SQL Solution
Problem
LeetCode SQL Problem
- Reported Posts II
Actions table
| user_id | post_id | action_date | action | extra |
|---|---|---|---|---|
| 1 | 1 | 2019-07-01 | view | null |
| 1 | 1 | 2019-07-01 | like | null |
| 1 | 1 | 2019-07-01 | share | null |
| 2 | 2 | 2019-07-04 | view | null |
| 2 | 2 | 2019-07-04 | report | spam |
| 3 | 4 | 2019-07-04 | view | null |
| 3 | 4 | 2019-07-04 | report | spam |
| 4 | 3 | 2019-07-02 | view | null |
| 4 | 3 | 2019-07-02 | report | spam |
| 5 | 2 | 2019-07-03 | view | null |
| 5 | 2 | 2019-07-03 | report | racism |
| 5 | 5 | 2019-07-03 | view | null |
| 5 | 5 | 2019-07-03 | report | racism |
Removals table
| post_id | remove_date |
|---|---|
| 2 | 2019-07-20 |
| 3 | 2019-07-18 |
Solution
- MySQL
- TSQL
Reported Posts II
WITH PostRemovalRate
AS (
-- Perform a LEFT JOIN so we can calculate daily removal rate of reported spams
SELECT A.action_date
,count(R.post_id) / cast(count(A.post_id) AS FLOAT) AS removal_rate
FROM Actions AS A
LEFT JOIN Removals AS R ON A.post_id = R.post_id
WHERE A.action = 'report'
AND A.extra = 'spam'
GROUP BY A.action_date
)
-- Find the average for daily percentage of posts that got removed after being reported as spam
-- Round to 2 decimal places
SELECT round(avg(removal_rate), 2) * 100 AS average_daily_percent
FROM PostRemovalRate
Reported Posts II
WITH PostRemovalRate
AS (
-- Perform a LEFT JOIN so we can calculate daily removal rate of reported spams
SELECT A.action_date
,count(R.post_id) / cast(count(A.post_id) AS FLOAT) AS removal_rate
FROM Actions AS A
LEFT JOIN Removals AS R ON A.post_id = R.post_id
WHERE A.action = 'report'
AND A.extra = 'spam'
GROUP BY A.action_date
)
-- Find the average for daily percentage of posts that got removed after being reported as spam
-- Round to 2 decimal places
SELECT round(avg(removal_rate), 2) * 100 AS average_daily_percent
FROM PostRemovalRate
Query Output
| average_daily_percent |
|---|
| 75 |