SQL167 连续签到领金币

题目描述

用户行为日志表 tb_user_log

id	uid	artical_id	in_time	out_time	sign_in
1	101	0	2021-07-07 10:00:00	2021-07-07 10:00:09	1
2	101	0	2021-07-08 10:00:00	2021-07-08 10:00:09	1
3	101	0	2021-07-09 10:00:00	2021-07-09 10:00:42	1
4	101	0	2021-07-10 10:00:00	2021-07-10 10:00:09	1
5	101	0	2021-07-11 23:59:55	2021-07-11 23:59:59	1
6	101	0	2021-07-12 10:00:28	2021-07-12 10:00:50	1
7	101	0	2021-07-13 10:00:28	2021-07-13 10:00:50	1
8	102	0	2021-10-01 10:00:28	2021-10-01 10:00:50	1
9	102	0	2021-10-02 10:00:01	2021-10-02 10:01:50	1
10	102	0	2021-10-03 10:00:55	2021-10-03 11:00:59	1
11	102	0	2021-10-04 10:00:45	2021-10-04 11:00:55	0
12	102	0	2021-10-05 10:00:53	2021-10-05 11:00:59	1
13	102	0	2021-10-06 10:00:45	2021-10-06 11:00:55	1

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明

• artical_id-文章ID代表用户浏览的文章的ID，特殊情况artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。注意：只有artical_id为0时sign_in值才有效。
• 从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
• 每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）

问题

计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。结果按月份、ID升序排序。

注：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了。

输出示例

示例数据的输出结果如下：

uid	month	coin
101	202107	15
102	202110	7

解释：

101在活动期内连续签到了7天，因此获得1*7+2+6=15金币；

102在10.01~10.03连续签到3天获得5金币

10.04断签了，10.05~10.06连续签到2天获得2金币，共得到7金币。

---

思路

参考大佬：题解 | #连续签到领金币# 解题思路总结_牛客博客

问题理解

我们需要计算每个用户在2021年7月至10月期间，每月通过签到获得的金币总数。签到规则如下：

1. 每天签到可获得1金币
2. 连续签到第3天额外获得2金币
3. 连续签到第7天额外获得6金币
4. 每连续签到7天后重新开始计算（第8天视为新一轮的第1天）

解题思路

步骤1：提取有效签到记录

首先需要从用户行为日志中筛选出符合条件的签到记录：

• 时间范围：2021-07-07至2021-10-31
• 文章ID为0（表示在非文章页面）
• 签到位为1（表示成功签到）

SELECT DISTINCTuid,DATE(in_time) AS sign_date
FROM tb_user_log
WHERE in_time BETWEEN '2021-07-07 00:00' AND '2021-10-31 23:59'AND artical_id = 0AND sign_in = 1

步骤2：识别连续签到分组

为了计算连续签到天数，我们使用一个技巧：用签到日期减去行号，相同结果的日期属于同一连续签到组。

SELECT uid,sign_date,DATE_SUB(sign_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY uid ORDER BY sign_date) DAY) AS group_date
FROM sign_dates

例如：

• 用户连续签到3天：2021-07-07、2021-07-08、2021-07-09
• 计算 group_date：
  • 2021-07-07 - 1天 = 2021-07-06
  • 2021-07-08 - 2天 = 2021-07-06
  • 2021-07-09 - 3天 = 2021-07-06
    这三个日期的 group_date 相同，表示它们是连续的

步骤3：计算连续签到天数

在每组内，按日期排序并编号，得到连续签到天数。

SELECT uid,sign_date,ROW_NUMBER() OVER (PARTITION BY uid, group_date ORDER BY sign_date) AS continuous_day
FROM sign_groups

步骤4：计算每日金币

根据连续签到天数计算每日获得的金币：

• 第3天：1基础金币 + 2额外 = 3金币
• 第7天：1基础金币 + 6额外 = 7金币
• 其他天数：1金币

SELECT uid,sign_date,CASE WHEN continuous_day % 7 = 3 THEN 3WHEN continuous_day % 7 = 0 THEN 7ELSE 1END AS coin
FROM continuous_days

步骤5：按月汇总金币

最后，按用户ID和月份分组，汇总金币总数。

SELECT uid,DATE_FORMAT(sign_date, '%Y%m') AS month,SUM(coin) AS coin
FROM daily_coins
GROUP BY uid, DATE_FORMAT(sign_date, '%Y%m')
ORDER BY month, uid

关键点

1. ​连续签到识别​：通过日期减去行号的方法识别连续签到组
2. ​金币计算规则​：正确处理第3天和第7天的额外金币
3. ​7天周期重置​：使用模运算(continuous_day % 7)实现7天后重置

---

代码

WITH 
-- 获取有效的签到日期
sign_dates AS (SELECT DISTINCTuid,DATE(in_time) AS sign_dateFROM tb_user_logWHERE in_time BETWEEN '2021-07-07 00:00' AND '2021-10-31 23:59'AND artical_id = 0AND sign_in = 1
),-- 计算连续签到分组
sign_groups AS (SELECT uid,sign_date,-- 计算连续签到分组标识DATE_SUB(sign_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY uid ORDER BY sign_date) DAY) AS group_dateFROM sign_dates
),-- 计算每组内的连续签到天数
continuous_days AS (SELECT uid,sign_date,-- 计算每组内的连续签到天数ROW_NUMBER() OVER (PARTITION BY uid, group_date ORDER BY sign_date) AS continuous_dayFROM sign_groups
),-- 计算每日金币
daily_coins AS (SELECT uid,sign_date,CASE WHEN continuous_day % 7 = 3 THEN 3  -- 第3天额外2金币WHEN continuous_day % 7 = 0 THEN 7  -- 第7天额外6金币ELSE 1                              -- 其他天1金币END AS coinFROM continuous_days
)-- 最终按月汇总
SELECT uid,DATE_FORMAT(sign_date, '%Y%m') AS month,SUM(coin) AS coin
FROM daily_coins
GROUP BY uid, DATE_FORMAT(sign_date, '%Y%m')
ORDER BY month, uid;