题解 | #连续签到领金币#

# 连续签到，根据注的内容，应该只记进入时间，忽略离开时间，
# lead开窗函数，用户分页，新增一列是对应日期的下一个记录的日期
# 两列相减，如果是1，那就算连续；不是1，那就是不连续
# 从第一个1开始到第三个1，就是2， 到第七个就是6
# 日期：7.1 到 10.31 # 错了，是从7.7

# 原来直接date就能提取出日期

#以上是本来的想法

WITH temp_0 AS(
    SELECT DISTINCT uid, 
        DATE(in_time) date_now,  
        DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE(in_time)) rn
    FROM tb_user_log
    WHERE (artical_id = 0) AND (DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31') AND (sign_in = 1)
    ),
    temp_1 AS(
    SELECT uid, date_now, 
        CASE DENSE_RANK() OVER(PARTITION BY DATE_SUB(date_now, INTERVAL rn DAY), uid ORDER BY date_now) % 7
            WHEN 3 THEN 3
            WHEN 0 THEN 7
            ELSE 1 END cnt
    FROM temp_0
    )

SELECT uid, DATE_FORMAT(date_now, '%Y%m'), SUM(cnt)
FROM temp_1
GROUP BY uid, DATE_FORMAT(date_now, '%Y%m')
ORDER BY DATE_FORMAT(date_now, '%Y%m'), uid;

借鉴的第一个题解

梳理下思路，连续签到，根据注的内容，应该只记进入时间，忽略离开时间。日期：7.7 到 10.31

1.因此可以先查询必要信息，包括去重uid, in_time的日期，还有所有日期的排名（这里是为了后面计算日期的排名）

想想，如果是连续递增的，那么in_time的日期减去排名得到的日期都是一样的，由此得以判断是否连续。

第一次查询，就要根据题意把符合要求的记录提取出来，在where语句，日期、文章id，sign等，一定要注意日期是7.7开始，不是7.1.利用思维惯性埋坑了

2.临时表1，继续查询uid, in_time的日期, 根据所有日期的排名，用当天的日期减去排名数，使得连续的记录的计算得到的日期是相同的；再利用这个相同的日期和uid分页开窗，用当天的日期作为排序正排，得到了每个用户在同一周期的排序（周期是指1-7）。周期很重要，第一次查询得到的排名是每个用户的，没有限制周期，现在有了连续记录的判断依据，就能计算周期。

再将周期性排序的结果对7取余，如果余数是3，说明连续登录3天了，该多给2个金币，也就是3个；余数为0，就是连续登录7天了，同理给7个。这样求得了每个记录该给几个金币

3.主查询，把日期降维到年月，分组求和即可

重点是怎么知道又是一个新周期了，怎么确定3和7。那位大佬采用了排序的方式，通过排名的有序性和连续的日期相同解决了这个问题。