SQL167 连续签到领金币

SQL167 连续签到领金币

题目描述

用户行为日志表 tb_user_log

（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对应的日期签到了。

输出示例

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

解释：

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 DISTINCT
    uid,
    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 = 0
  AND 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 3
        WHEN continuous_day % 7 = 0 THEN 7
        ELSE 1
    END 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 DISTINCT
        uid,
        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 = 0
      AND 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_date
    FROM sign_dates
),

-- 计算每组内的连续签到天数
continuous_days AS (
    SELECT 
        uid,
        sign_date,
        -- 计算每组内的连续签到天数
        ROW_NUMBER() OVER (PARTITION BY uid, group_date ORDER BY sign_date) AS continuous_day
    FROM 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 coin
    FROM 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;