题解 | #异常的邮件概率#

异常的邮件概率

https://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e

先列大思路:

  • 找出 receive_id和send_id 都是正常用户的id行(is_blacklist=0)
  • 统计type=completed 的次数/所有次数

解法1是我自己写的第一版。整体比较冗长

select a.date,round(1-b.count/a.count,3) as p
from 
(
    select date,count(*) as count
    from email
    where send_id not in (
        select id
        from user
        where is_blacklist=1
    )
    and receive_id not in (
        select id
        from user
        where is_blacklist=1
    )
    group by date
    # 所有正常的发送次数
)a
join (
    # 所有正常的用户、且type是成功的次数
    select date,count(*) as count
    from email
    where send_id not in (
        select id
        from user
        where is_blacklist=1
    )
    and receive_id not in (
        select id
        from user
        where is_blacklist=1
    )
    and type='completed'
    group by date
    
)b
on a.date=b.date
order by a.date asc

解法2是参考了评论的思路,更简单一些。是我要努力的方向!

select
e.date,
round(sum(case when e.type='completed' then 0 else 1 end)*1.0 / count(e.type),3) as p
from email as e
	join user as u1 on (e.send_id=u1.id and u1.is_blacklist=0)
	join user as u2 on (e.receive_id=u2.id and u2.is_blacklist=0)
group by e.date
order by e.date asc



#sql练习日常#
全部评论

相关推荐

04-06 11:24
已编辑
太原学院 C++
真烦好烦真烦:感觉不太对劲,这种主动加微信的一般都是坑,要小心辨别
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客企业服务