校招算法笔面试 | SQL笔试面试编程题-异常的邮件概率-优快云博客

题目

题目链接

这道题目要求我们计算正常用户之间邮件发送失败的概率。我们要做的事情如下：

1. 确定总体问题

我们需要从邮件表中提取数据，计算每个日期中正常用户发送给正常用户的邮件失败概率，并按日期升序排列输出。
邮件发送失败的概率是指在某个日期内，邮件发送失败的次数占总发送次数的比例。

2. 分析关键问题

筛选正常用户的邮件：通过子查询筛选出发送者和接收者均为正常用户的邮件。
计算失败概率：使用CASE语句计算邮件失败的次数，并除以总邮件数得到失败概率。
格式化输出：使用ROUND函数保留三位小数，并按日期升序排列输出。

3. 解决每个关键问题的代码及讲解

步骤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)

send_id IN (SELECT id FROM user WHERE is_blacklist = 0): 筛选出发送者为正常用户的邮件。
receive_id IN (SELECT id FROM user WHERE is_blacklist = 0): 筛选出接收者为正常用户的邮件。

步骤2：计算失败概率

我们使用CASE语句计算邮件失败的次数，并除以总邮件数得到失败概率：

round(sum(case when type = 'completed' then 0 else 1 end)/count(*),3) as p

CASE WHEN type = 'completed' THEN 0 ELSE 1 END: 计算邮件失败的次数。
SUM(...) / COUNT(*): 计算失败概率。
ROUND(..., 3): 保留三位小数。

步骤3：格式化输出

我们按日期升序排列输出结果：

group by 
    date
order by
    date asc

GROUP BY date: 按日期分组。
ORDER BY date ASC: 按日期升序排列。

完整代码

select 
    date,
    round(sum(case when type = 'completed' then 0 else 1 end)/count(*),3) as 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 asc;