力扣1132(MySQL)-报告的记录Ⅱ（中等）

题目：

编写一段 SQL 来查找：在被报告为垃圾广告的帖子中，被移除的帖子的每日平均占比，四舍五入到小数点后 2 位。

Actions 表：

 Removals 表：

Result 表：

2019-07-04 的垃圾广告移除率是 50%，因为有两张帖子被报告为垃圾广告，但只有一个得到移除。
2019-07-02 的垃圾广告移除率是 100%，因为有一张帖子被举报为垃圾广告并得到移除。
其余几天没有收到垃圾广告的举报，因此平均值为：(50 + 100) / 2 = 75%
注意，输出仅需要一个平均值即可，我们并不关注移除操作的日期。

建表语句：

Create table If Not Exists actions_1132 (
    user_id int, 
        post_id int,
        action_date date, 
        action enum('view', 'like', 'reaction', 'comment', 'report', 'share'),
        extra varchar(6)
);
Create table If Not Exists removals_1132 (
        post_id int,
        remove_date date
    );
Truncate table actions_1132;
insert into actions_1132 (user_id, post_id, action_date, action, extra) values 
('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');
Truncate table removals_1132;
insert into removals_1132 (post_id, remove_date) values ('2', '2019-07-20'),( '3', '2019-07-18');

解题思路：

①先筛选出actions表中被报道为垃圾信息即extra = 'spam'的数据；

select post_id, action_date
from actions_1132 
where extra = 'spam'

②将第一步查询出来的表通过post_id与 removals左连接；

select *
from (
    select post_id, action_date
    from actions_1132 
    where extra = 'spam'
  ) as a
left join removals_1132 b
on a.post_id = b.post_id

 ③然后再根据第二步查询出的临时表，以action_date为分组，统计出删除比率；

  select action_date,count(distinct b.post_id) / count(distinct a.post_id) as rate
  from (
    select post_id, action_date
    from actions_1132 
    where extra = 'spam'
  ) as a
  left join removals_1132 b
  on a.post_id = b.post_id
  group by action_date

 ④最后根据上一步的临时表，计算出被移除的帖子的每日平均占比；

select round(sum(rate)/ count(*)*100, 2) as average_daily_percent
from (
  select action_date,count(distinct b.post_id) / count(distinct a.post_id) as rate
  from (
    select post_id, action_date
    from actions_1132 
    where extra = 'spam'
  ) as a
  left join removals_1132 b
  on a.post_id = b.post_id
  group by action_date
) as temp