百度一面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);
思路分析
- 根据题目"连续"二字可知,此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')
;