Leetcode-DB-MySQL/1132. Reported Posts II.sql at master · espresso98/Leetcode-DB-MySQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- Write an SQL query to find the average daily percentage of posts that got removed after being reported as spam, rounded to 2 decimal places.

SELECT ROUND(AVG(cnt_pct), 2) AS average_daily_percent
FROM (
    SELECT (COUNT(DISTINCT r.post_id) / COUNT(DISTINCT a.post_id)) * 100  AS cnt_pct
    FROM Actions a
    LEFT JOIN Removals r ON a.post_id = r.post_id
    WHERE extra='spam'
    GROUP BY action_date) tmp


-- Input:
-- 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  |
-- +---------+-------------+
-- Output:
-- +-----------------------+
-- | average_daily_percent |
-- +-----------------------+
-- | 75.00                 |
-- +-----------------------+


-- SELECT a.post_id, action_date, extra, COUNT(DISTINCT r.post_id), COUNT(DISTINCT a.post_id), (COUNT(DISTINCT r.post_id)/ COUNT(DISTINCT a.post_id)) * 100  AS cnt_pct
--     FROM Actions a
--     LEFT JOIN Removals r
--     ON a.post_id = r.post_id
--     WHERE extra='spam'
--     GROUP BY action_date
-- ORDER BY a.post_id

-- | post_id | action_date | extra | COUNT(DISTINCT r.post_id) | COUNT(DISTINCT a.post_id) | cnt_pct |
-- | ------- | ----------- | ----- | ------------------------- | ------------------------- | ------- |
-- | 2       | 2019-07-04  | spam  | 1                         | 2                         | 50      |
-- | 3       | 2019-07-02  | spam  | 1                         | 1                         | 100     |