百度一面SQL之连续签到领金币

SQL题目

来自牛客SQL题库

已知一张用户行为日志表tb_user_log，字段包括uid-用户ID、artical_id-文章ID、in_time-进入时间、out_time-离开时间、sign_in-是否签到
注意1：只有artical_id为0时sign_in值才有效；
注意2：从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币，每连续签到7天后重新累积签到天数
问题：计算每个用户2021年7月至10月每月获得的金币数

答案解析

模拟数据

CREATE TABLE tb_user_log (
    uid INT COMMENT '用户ID',
    artical_id INT COMMENT '视频ID',
    in_time varchar(20) COMMENT '进入时间',
    out_time varchar(20) COMMENT '离开时间',
    sign_in int COMMENT '是否签到'
) ;
 
INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 0, '2021-07-07 10:00:00', '2021-07-07 10:00:09', 1),
  (101, 0, '2021-07-08 10:00:00', '2021-07-08 10:00:09', 1),
  (101, 0, '2021-07-09 10:00:00', '2021-07-09 10:00:42', 1),
  (101, 0, '2021-07-10 10:00:00', '2021-07-10 10:00:09', 1),
  (101, 0, '2021-07-11 23:59:55', '2021-07-11 23:59:59', 1),
  (101, 0, '2021-07-12 10:00:28', '2021-07-12 10:00:50', 1),
  (101, 0, '2021-07-13 10:00:28', '2021-07-13 10:00:50', 1),
  (101, 0, '2021-07-14 11:00:28', '2021-07-14 11:00:50', 1),
  (101, 0, '2021-07-15 11:59:28', '2021-07-16 00:01:20', 1),
  (102, 0, '2021-10-01 10:00:28', '2021-10-01 10:00:50', 1),
  (102, 0, '2021-10-02 10:00:01', '2021-10-02 10:01:50', 1),
  (102, 0, '2021-10-03 11:00:55', '2021-10-03 11:00:59', 1),
  (102, 0, '2021-10-04 11:00:45', '2021-10-04 11:00:55', 0),
  (102, 0, '2021-10-05 11:00:53', '2021-10-05 11:00:59', 1),
  (102, 0, '2021-10-06 11:00:45', '2021-10-06 11:00:55', 1);

alt

思路分析

根据题目"连续"二字可知，此SQL题在考察连续问题，可以套用本人总结的连续问题模板（见之前的文章）
需求拆解如下：
- 题目要求每个月用户领金币的数量，假设知道用户每日可以领到的金币数量，问题就迎刃而解了
- 要想求每日用户领到的金币数量，只需要关注三个特殊节点，连续3天签到+2个金币，连续7天签到+6个金币，7天后领取金币数量为1
- 如何判断是否连续签到3天/7天？
- 固定套路：首先对用户按照签到时间进行排序得到rn1，然后按照用户和签到时间减去rn1进行分组，签到时间进行排序得到rn2
- 得到rn2之后，就可以很容易得到每日用户签到领取的金币数量

具体代码

SELECT
	uid,DATE_FORMAT(dt,'%Y%m') `month`, sum(day_coin) coin  
FROM
	( 
		SELECT
	*,
	DATE_SUB(dt,INTERVAL rn day) dt_tmp, 
	case row_number() over(PARTITION BY DATE_SUB(dt,INTERVAL rn day),uid ORDER BY dt )%7 
		WHEN 3 THEN 3
		WHEN 0 THEN 7
		ELSE 1
	END as day_coin 
	FROM (
			SELECT
		uid,
		date(in_time) dt,
		row_number() over(partition BY uid ORDER BY date(in_time)) rn 
	FROM
		tb_user_log
	WHERE
		DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31' AND artical_id = 0 AND sign_in = 1
	) t
) t
GROUP BY
	uid, DATE_FORMAT(dt,'%Y%m')
;

alt