SQL 65) -CASE WHEN 统计正常用户发送给正常用户邮件失败的概率

方法）CASE WHEN + GROUP BY

• 结果保留到小数点后面3位(3位之后的四舍五入)
• 按照日期升序排序

SELECT date, 
ROUND(SUM(CASE type WHEN 'completed' THEN 0 ELSE 1 END)*1.0/ COUNT(type),3
)
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 
ORDER BY date;