欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

牛客SQL练习第66题

程序员文章站 2022-06-07 11:42:46
...

题目描述

现在有一个需求,让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表,id为主键, type是枚举类型,枚举成员为(completed,no_completed),completed代表邮件发送是成功的,no_completed代表邮件是发送失败的。简况如下:

牛客SQL练习第66题

第1行表示为id为2的用户在2020-01-11成功发送了一封邮件给了id为3的用户;

第3行表示为id为1的用户在2020-01-11没有成功发送一封邮件给了id为4的用户;

第6行表示为id为4的用户在2020-01-12成功发送了一封邮件给了id为1的用户;

下面是一个用户(user)表,id为主键,is_blacklist为0代表为正常用户,is_blacklist为1代表为黑名单用户,简况如下:
牛客SQL练习第66题

第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户,是黑名单用户,如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户
。。。
第4行表示id为4的是正常用户

现在让你写一个sql查询,每一个日期里面,正常用户发送给正常用户邮件失败的概率是多少,结果最多保留到小数点后面3位(3位之后的四舍五入),并且按照日期升序排序,上面例子查询结果如下:

牛客SQL练习第66题

结果表示:
2020-01-11失败的概率为0.500,因为email的第1条数据,发送的用户id为2是黑名单用户,所以不计入统计,正常用户发正常用户总共2次,但是失败了1次,所以概率是0.500;
2020-01-12没有失败的情况,所以概率为0.000.
(注意: sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5,sqlite四舍五入的函数为round)

解答:

筛选出接收方都是合法用户的记录,然后利用group by + case语句来求失败记录以及总发送次数并相除,最后再四舍五入即可

select date,round(sum(case e.type when 'no_completed' then 1 else 0 end)*1.0/count(e.type),3) as p 
from email e
where send_id in (select id from user where is_blacklist = 0)
and receive_id in (select id from user where is_blacklist = 0)
group by date;

相关标签: 牛客 sql