SQL练习65：异常的邮件概率

题目链接：牛客网

题目描述 现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率: 有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下: 第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代表为黑名单用户，简况如下: 第1行表示id为1的是正常用户; 第2行表示id为2的不是正常用户，是黑名单用户，如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户 。。。 第4行表示id为4的是正常用户

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

解法 先通过子查询的方式获取正常人给正常人发邮件的email表，再通ROUND函数保留三位小数，AVG(type = 'no_completed')的到失败的概率，最后对date进行分组即可。

SELECT date, round(AVG(type = 'no_completed'), 3) p
FROM (SELECT *
      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))as r
GROUP BY date