题解 | SQL264 异常的邮件概率

WITH t1 AS (
    -- 第一步，先找出黑名单中有谁？
	SELECT 
    	id
    FROM user
    WHERE is_blacklist = 1
),
t2 AS (
    -- 第二步，找出正常用户->正常用户的记录！
	SELECT 
    	send_id,
    	receive_id,
    	type,
    	date
    FROM email
    WHERE send_id NOT IN (SELECT id FROM t1)
    AND receive_id NOT IN (SELECT id FROM t1)
),
t3 AS (
    SELECT 
    	date,
    	ROUND(
            SUM(
                IF(type = 'no_completed',1,0)
            )/ COUNT(type)
        ,3) AS P
    FROM t2
    GROUP BY date
    ORDER BY DATE ASC
)
SELECT * FROM t3;

# 注意进行日期升序排序！