题解 | #异常的邮件概率#
异常的邮件概率
https://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
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
