关于根据活跃度给用户分类的SQL语句编写和优化

前言

2022.01.25
用了一个小时完成了牛客网上面的一道题，从35行代码改到23行，附上过程和一些思考总结。

---

题目概述

描述
用户行为日志表tb_user_log
（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

注：
用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
假设今天就是数据中所有日期的最大值。
近7天表示包含当天T的近7天，即闭区间[T-6, T]。

总思路

1.得到用户首次登录时间（因为要计算新增）
2.得到用户最近一次的登录时间（因为要算近7天）
3.通过这两个时间去case when用户进行分类
其中，得到首次登录时间和最近一次登录时间的方法为分两次开窗排序（升序和降序）再把两个表连接起来
最初版本的代码如下：

SELECT
    user_grade,
    round(count(uid)/(SELECT COUNT(DISTINCT uid) cnt FROM tb_user_log),2) as ratio
FROM(
    SELECT
        uid,
        case
            when datediff('2021-11-04',recent_dt) <= 6 and datediff