题解 | 异常的邮件概率

-- 首先找到正常用户，然后根据日期分组，最后用sum(IF)去求失败概率
WITH white_user AS(
    SELECT DISTINCT id
    FROM user
    WHERE is_blacklist = '0'
)

SELECT 
    date,
    ROUND(SUM(IF(type = 'no_completed',1,0))/COUNT(*),3) p
FROM email
WHERE send_id IN(
    SELECT id
    FROM white_user
)
AND receive_id IN(
    SELECT id
    FROM white_user
)
GROUP BY date