SELECT date,
    ROUND(
        AVG(IF(type='no_completed',1,0))
    ,3) p
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
)
GROUP BY date
ORDER BY date

1、找到正常用户的发送和接收

WHERE send_id IN (
    SELECT id
    FROM user
    WHERE is_blacklist = 0
) AND receive_id IN (
    SELECT id
    FROM user
    WHERE is_blacklist = 0
)

2、计算每个日期未完成的概率

    ROUND(
        AVG(IF(type='no_completed',1,0))
    ,3) p
	
	GROUP BY date

3、按日期进行排序

SELECT date,
    ROUND(
        AVG(IF(type='no_completed',1,0))
    ,3) p
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
)
GROUP BY date
ORDER BY date