SQL大厂面试题-连续签到领金币场景

1.问题背景

有用户签到记录表，use_signing,记录用户当天是否完成签到,请计算出每个用户的每个月获得的金币数量；

签到领金币规则如下：

1. 用户签到获得1金币；
2. 如果用户连续签到3天则第三天获得2金币，如果用户连续签到7天则第7天获得5金币；
3. 连续签到7天后连续天数重置，每月签到天数重置；

2.数据准备

CREATE TABLE IF NOT EXISTS user_signing (
    user_id VARCHAR(10) NOT NULL,
    signing_date DATE NOT NULL,
    is_sign TINYINT(1) NOT NULL,
    PRIMARY KEY (user_id, signing_date)
);

-- 插入示例数据
INSERT INTO user_signing (user_id, signing_date, is_sign) VALUES
('001', '2024-01-01', 1),
('001', '2024-01-02', 1),
('001', '2024-01-03', 1),
('001', '2024-01-04', 0),
('001', '2024-01-05', 1),
('001', '2024-01-06', 1),
('001', '2024-01-07', 1),
('001', '2024-01-08', 1),
('001', '2024-01-09', 1),
('001', '2024-01-10', 1),
('001', '2024-01-11', 1),
('001', '2024-01-12', 1),
('001', '2024-01-13', 1),
('001', '2024-01-14', 1),
('001', '2024-01-15', 1),
('001', '2024-01-16', 1),
('001', '2024-01-17', 1),
('001', '2024-01-18', 1),
('001', '2024-01-19', 1),
('001', '2024-01-20', 0),
('001', '2024-01-21', 1),
('001', '2024-01-22', 1),
('001', '2024-01-23', 1),
('001', '2024-01-24', 0),
('001', '2024-01-25', 1),
('001', '2024-01-26', 1),
('001', '2024-01-27', 1),
('001', '2024-01-28', 1),
('001', '2024-01-29', 0),
('001', '2024-01-30', 1),
('001', '2024-01-31', 1),
('001', '2024-02-01', 1),
('001', '2024-02-02', 1),
('001', '2024-02-03', 1),
('001', '2024-02-04', 1),
('001', '2024-02-05', 1),
('001', '2024-02-06', 1),
('001', '2024-02-07', 1),
('001', '2024-02-08', 1),
('001', '2024-02-09', 1),
('001', '2024-02-10', 1);

3.解题思路

• 提取月份和标识区块： 在第一步 (t1)，使用 SUBSTR 函数/DATE_FORMAT函数提取签到日期中的月份，并通过 SUM 和条件判断 (IF) 将每个用户按月分为若干连续签到的区块 (df)，区分签到和未签到的记录。

• 计算连续签到天数： 在第二步 (t2)，利用窗口函数 ROW_NUMBER，对每个用户的每个月份内按 df 分组，对连续的签到天数进行编号。这个编号帮助识别每一段连续的签到记录。

• 处理连续7天的重置： 第三步 (t3) 使用 MOD 函数处理连续签到达到 7 天的情况（关键点）。因为任务要求每达到7天时需要重置连续天数，因此通过取余数确保第8天开始重新计算天数。

• 金币奖励计算： 在第四步 (t4)，根据连续签到的天数来分配金币。具体逻辑是：

  • 连续签到满 3 天奖励 2 枚金币
  • 连续签到满 7 天奖励 5 枚金币
  • 其他情况下签到获得 1 枚金币，没有签到则为 0 枚。

• 统计金币： 最后，在主查询中，通过窗口函数计算每个用户累计获得的金币总数和每月获得的金币总数。SUM 函数配合 OVER 子句，按签到日期或月份对金币进行累积计算。

4.解决问题

WITH t1 as(
-- 利用文本函数，提取出月份/或者还可以用日期函数date_format
-- 然后借用is_sign将记录分为连续的几块
	SELECT user_id
				,signing_date
				,is_sign
				,SUBSTR(signing_date,1,7) AS month_date
                -- 或者可以用 ,DATE_FORMAT(signing_date,'%y-%m') AS month_date
				,SUM(if(is_sign = 1,0,1))OVER(PARTITION BY user_id,SUBSTR(signing_date,1,7) ORDER BY user_id,signing_date) as df
				FROM mci.user_signing
),
t2 as(
-- 这一块主要是利用窗口函数row_number计算出每个登录日对应的连续签到天数			
	SELECT user_id
	,signing_date
	,month_date
    ,is_sign
	,row_number()over(partition by user_id,month_date,df ORDER BY signing_date)as rn
	FROM t1
	WHERE is_sign =1
),
t3 as(
-- 这一块主要解决一旦连续天数到达7天，则重置连续天数
-- 这个地方用了取余数的函数
	SELECT user_id
	,signing_date
	,month_date
    ,is_sign
	,rn
	,if(mod(rn,7)=0,7,mod(rn,7)) as final_rn
	FROM t2
),
t4 as(
-- 这一块主要是根据连续天数来确定奖励金币的多少
	SELECT us.user_id
	,us.signing_date
    ,us.is_sign
    ,substr(us.signing_date,1,7) as month_date
	,if(t3.final_rn IS NULL,0,t3.final_rn) as eve_rn
	,CASE WHEN if(t3.final_rn IS NULL,0,t3.final_rn) = 3 then 2
		  WHEN if(t3.final_rn IS NULL,0,t3.final_rn) = 7 then 5
		  WHEN if(t3.final_rn IS NULL,0,t3.final_rn) = 0 then 0
     ELSE 1 END as coin_get
	 FROM user_signinG us
	 LEFT JOIN t3 ON t3.user_id = us.user_id AND t3.signing_date = us.signing_date
)
SELECT 
 user_id
,signing_date -- 签到日期
,month_date -- 签到月份
,is_sign -- 是否签到
,eve_rn  -- 截止当前连续签到的天数
,coin_get -- 当天获得的金币
,sum(coin_get)OVER (PARTITION BY user_id ORDER BY signing_date) as accu_coins -- 累计获得金币
,sum(coin_get)OVER(PARTITION BY user_id,month_date) as month_acc_coins -- 当月获得的金币
FROM t4

• 代码块一t1

• 代码块二t2

SELECT user_id
			,signing_date
			,month_date
      ,is_sign
			,row_number()over(partition by user_id,month_date,df ORDER BY signing_date)as rn
FROM(
				SELECT user_id
				,signing_date
				,is_sign
				,SUBSTR(signing_date,1,7) AS month_date
				,SUM(if(is_sign = 1,0,1))OVER(PARTITION BY user_id,SUBSTR(signing_date,1,7) 
									ORDER BY user_id,signing_date) as df
				FROM mci.user_signing
)t1
WHERE is_sign =1

• 代码块三t3

SELECT user_id
		,signing_date
		,month_date
    ,is_sign
		,rn
		,if(mod(rn,7)=0,7,mod(rn,7)) as final_rn
FROM(
		SELECT user_id
			,signing_date
			,month_date
            ,is_sign
			,row_number()over(partition by user_id,month_date,df ORDER BY signing_date)as rn
		FROM(
				SELECT user_id
				,signing_date
				,is_sign
				,SUBSTR(signing_date,1,7) AS month_date
				,SUM(if(is_sign = 1,0,1))OVER(PARTITION BY user_id,SUBSTR(signing_date,1,7) ORDER BY user_id,signing_date) as df
				FROM mci.user_signing
		)t1
		WHERE is_sign =1
)t2