题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/reported-posts-ii
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
本人思路:
1、将actions表与removals表根据条件a.post_id = r.post_id左连接 ,并且筛选出 extra = 'spam’行
2、再根据时间分组,来统计 a.post_id与r.post_id个数,count(a.post_id)是应该删除的邮件数,count(r.post_id)是根据removals表能删除的邮件数,注意count(a.post_id)和count(r.post_id)要去重
3、由2得到临时表,从中可以计算出被移除的帖子的每日平均占比
select round(sum(rate)/count(action_date)*100,2)as average_daily_percent
from
(select action_date,count(distinct r.post_id)/count(distinct a.post_id) as rate
-- 注意distinct
from actions as a left join removals as r
on a.post_id = r.post_id
where extra = 'spam'
group by action_date )tmp