思路:

  1. 因为必须要是正常用户,所以需要判断发送者和接收者必须都为正常用户,即 is_blacklist = 0,以此来排除黑名单用户
  2. 再根据日期分组,求出失败的概率:发送不成功的次数/count(日期)
  3. 最后根据日期升序排序

完整代码:

SELECT date, ROUND(SUM(type = 'no_completed') / COUNT(*), 3) AS p
FROM email 
JOIN user AS u1
ON email.send_id = u1.id AND u1.is_blacklist = 0
JOIN user AS u2
ON email.receive_id = u2.id AND u2.is_blacklist = 0
GROUP BY date
ORDER BY date;