题解 | #异常的邮件概率#

-- 定义名为 a 的公用表表达式，包含符合条件的 email 表数据
WITH a AS (
    SELECT *
    FROM email
    WHERE send_id IN (
        SELECT id
        FROM user
        WHERE is_blacklist = 0
    ) AND receive_id IN (
        SELECT id
        FROM user
        WHERE is_blacklist = 0
    )
)
-- 从公用表表达式 a 中进行查询
SELECT
    date,  -- 日期列
    ROUND(SUM(IF(type = 'no_completed', 1, 0)) / COUNT(*), 3) AS p  -- 计算未完成的比例并保留三位小数
FROM a
GROUP BY date  -- 按照日期进行分组
ORDER BY date;  -- 按照日期升序排序