牛客网：SQL 练习题（七）

昨天凌晨阿蒲发布了新歌《A Way To Die》，强推歌词！阿蒲真的是唱的越来越好了～就是…能不能出点…好唱一点的歌 ～～～（本来公众号是昨天发的，但是临时有事，只能推到今天了！PS：b站阿蒲教学英语了～他凭什么觉得我学的会，哈哈哈）

---

SQL264 异常的邮件概率

题目

解题思路
本题挺有意思的，事实上这种复杂的计算其实可以利用 SQL 查询 + python 解决，但是这里要求用纯 SQL 书写，那么我们就可以思考一下，它要求我们怎么做呢？

• 我们要如何找到不正常的用户？
• 我们要如何计算成功率？
• 成功率需要保留 3 位小数

由此展开，我个人的解题方法如下：

1. 通过 id 内连接两张表，并通过 is_blacklist 字段筛选非黑明单用户
2. 利用 CASE-WHEN 函数计算成功率
3. 进行排序

SELECT
    email.date,
    ROUND(
        SUM(
            CASE
                email.type
                WHEN 'completed' THEN 0
                ELSE 1
            END)*1.0/ COUNT(email.type),
        3
    ) AS p
FROM
    email
    INNER JOIN user AS u1 ON (email.send_id = u1.id)
    INNER JOIN user AS u2 ON (email.receive_id = u2.id)
WHERE u1.is_blacklist = 0 AND u2.is_blacklist = 0
GROUP BY
    email.date
ORDER BY
    email.date ASC;

这一题用到了连接子句，窗口函数等多种方法，是一道不错的综合题。
开胃小菜结束，接下来进入本文的主题：“牛客每个人最近的登录日期”系列题。

主题目

题目1

解题思路
本题要求查出每个用户最后一天登陆的日子，因此需要根据用户进行分组，并对每个组里的日子查找最大值，因此本题解题思路如下：

1. 利用 GROUP BY 子句对 user_id 进行分组
2. 针对分完组之后的数据，利用 MAX 函数求组内最大 date
3. 利用 ORDER BY 子句对 user_id 进行升序

SELECT user_id, MAX(date) AS id
FROM login
GROUP BY user_id
ORDER BY user_id ASC;

题目2

解题思路
本题要求查询每个用户最近一天登陆的日子，以及匹配其用户名和设备名，这是第一题的升级。
但是这里值得注意的是，如果只是在第一题的 SQL 语句上进行内联结，会违背 GROUP BY 的某个原则：除了聚合函数外的所有列都必须出现在 GROUP BY 子句中。
因此我们需要进行进一步的改写：

1. 根据相关字段内连接三张表，形成临时表
2. 针对临时表利用 WHERE 语句进行查找，并且把寻找最近一天登陆的日子的语句放在 WHERE 语句中
3. 利用 ORDER BY 子句对 name 进行升序

SELECT
    u.name AS u_n,
    c.name AS c_n,
    l.date
FROM login AS l
    INNER JOIN user AS u ON l.user_id = U.ID - Unique Digital Identity
    INNER JOIN client AS c ON l.client_id = c.id
WHERE (l.user_id, l.date) IN 
                (SELECT user_id, MAX(date) 
                FROM login
                GROUP BY user_id) 
ORDER BY u_n ASC;

题目3

解题思路
本题要求查询新登录用户的次日留存率。
首先我们需要知道留存率的计算：次日登陆的用户数/总用户数。
那么总用户数如何计算？利用 DISTINCT 函数计算
次日登陆的用户数如何计算？查找用户的最早登陆时间并往后推迟一天，查看这个时间点是否存在在数据中
由此，解题思路如下：

1. 通过 WHERE 语句查找次日登陆的用户数
2. 计算留存率
3. 排序

SELECT
    ROUND(COUNT(DISTINCT user_id) * 1.0
        / (SELECT COUNT(DISTINCT user_id) FROM login),
            3) AS p
FROM
    login
WHERE
    (user_id, date) IN (
        SELECT
            user_id,
            DATE_ADD(MIN(date),INTERVAL 1 DAY)
        FROM
            login
        GROUP BY
            user_id
    );

题目4

解题思路
本题要求查询每个日期登陆的新用户数。
注意：在给出的示例里，即使当天没有新用户数，也要计算，因此这里有一个易错点：如果我们根据每个用户的最早日期进行分组计算，并不会计算出 0 ，代码如下：

SELECT date, count(DISTINCT user_id) AS new
FROM login
WHERE (user_id, date) IN
(SELECT user_id, MIN(date) FROM login GROUP BY user_id)
GROUP BY date
ORDER BY date ASC
/*
运行结果为
date new
2020-10-12 3
2020-10-14 1
*/

那么我们怎么算才能把即使没有新用户，也要加入到结果里呢？这题的解法我参考了牛客网的大神解法：

1. 使用窗口函数为每个用户分配登录排名
2. 筛选每个用户的首次登录记录（即排序为1）
3. 根据日期进行分组

SELECT 
    date,
    SUM(CASE WHEN r=1 THEN 1 ELSE 0 END )AS new
FROM (
    SELECT 
        user_id,date,
        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY DATE) AS r
    FROM login) AS l
GROUP BY date
ORDER BY date

在这种解法里并不是利用筛选后的数据或者构建临时表筛选之后再进行计算，而是在原表的基础上扩充了新列，进行计算，这样从很大程度上保证了数据的完整性，因此能够计算出即使当日没有新增用户，也能展示出时间。

题目5

解题思路
本题要求找出每个日期下新用户的留存率。
留存率的计算：当日新用户数/前一日新用户数
如果前一日新用户数为0，那么当日新用户留存率为1，根据这一点需要明白：留存率的计算中可能存在无法计算的 0 值，因此需要用到 IFNULL 函数。
同时，前一日的新用户数如何计算？根据最早登陆的时间构建新的临时表；当日的新用户数如何计算？根据相同的 user_id，并且时间相差1天。
所以解题思路如下：

1. 构建新的临时表：
   根据 date 排序的表
   根据最早登陆的时间排序的表
   根据最早登陆的时间以及次日同用户登陆的表
2. 利用构建的临时表进行留存率计算
3. 按照 date 升序，并处理一些细节（如保留三位小数）

SELECT
    t0.date,
    IFNULL (
        ROUND(
            COUNT(DISTINCT t2.user_id) / (count(t1.user_id)),
            3
        ),
        0
    )
FROM (SELECT date FROM login GROUP BY date) AS t0
    LEFT JOIN (
            SELECT user_id, MIN(date) as date 
            FROM login GROUP BY user_id) AS t1 
            ON t0.date = t1.date
    LEFT JOIN login AS t2 
            ON t1.user_id = t2.user_id 
            AND DATEDIFF(t2.date, t1.date) = 1
GROUP BY t0.date

题目 6

牛客每天有很多人登录，请你统计一下牛客每个用户刷题情况，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但存在登录了没刷题的情况，不会存在刷题表里面，会存在提交代码没有通过的情况并记录在刷题表里，通过数目是0。

解题思路
本题需要求每个用户截止到某一天累计通过的题，那么显而易见，我们需要用到窗口函数。
这里需要注意的是，我们在计算过程中并没有用到 login！
我的解题结果如下：

1. 利用窗口函数找到每个用户的累积和
2. 连接多张表，补全所需信息
3. 针对 date, name 进行排序

SELECT DISTINCT
    u.name AS u_n,
    pn.date,
    SUM(pn.number) OVER (PARTITION BY U.ID - Unique Digital Identity ORDER BY pn.date) AS ps_num
FROM passing_number AS pn
    INNER JOIN user AS u ON pn.user_id = U.ID - Unique Digital Identity
ORDER BY pn.date ASC, u_n ASC;

个人总结

我们在解题 SQL 的时候，必然会有主要矛盾和次要矛盾，我们的关键是抓住主要矛盾，并解决它！然后再解决次要矛盾。
主要矛盾决定了本题能否被解决的关键！

---

题外话

关注我的微信公众号“曼珠沙华的生活”，将不定期更新
我建了一个关于失业的算法工程师的群，可以公众号后台回复“进群”，欢迎大家入群交流！（无广仅交流！）
关于SQL习题的智能体，我的链接可以给大家分享一下，欢迎使用：https://doubao.com/bot/u0Z9J2xn