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;