题解 | #连续签到领金币#
连续签到领金币
http://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
本题有2个难点：
- 解决经典问题 -- 连续签到
- 签到金币数量如何转化成代码语言
【难点1】连续签到天数
这是一个经典问题！面试中常会出现！必须熟练！
解题思路：
1、筛选出用户连续签到的dt
2、使用row_number()窗口函数按照dt升序进行排序
- row_number 排序结果是 1、2、3、4……
- rank 排序结果是 1、2、2、4……
- dense_rank 排序结果是 1、2、2、3……
3、如果签到日期是连续的，则签到日期-排序得到的日期相同。由此按照得到的日期进行分组，则能计算出用户各连续签到天数
【难点2】计算签到当日领取金币的数量
先来看领取金币数量规则
- 用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
- 每连续签到7天后重新累积签到天数
- 当签到天数%7=3 则领取3金币
- 当签到天数%7=0 则领取7金币
- 其余情况，领取1金币
突破以上2个难点后，就可以开始解题了~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
首先，筛选出各用户符合条件的签到日期，得到<签到表>。条件需要满足
- 签到日期为2021-07-07 0时至2021-10-31 24时
- artical_id=0
- and sign_in=1
- 签到只以in_time对应的日期为准
SELECT * FROM tb_user_log where date(in_time) between '2021-07-07' and '2021-10-31' and artical_id=0 and sign_in=1;其次，对签到表进行排序。使用窗口函数row_number()
- 以uid分组
- 按照date(in_time)排序
SELECT DISTINCT uid,date(in_time) dt, row_number() over (partition by uid order by date(in_time)) ranking FROM tb_user_log where date(in_time) between '2021-07-07' and '2021-10-31' and artical_id=0 and sign_in=1;然后，求出各用户连续登录的天数。用dt-ranking，得到日期相同的为一组。此组数量则为连续签到天数
SELECT DISTINCT uid,date(in_time) dt, row_number() over (partition by uid order by date(in_time)) ranking, date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2 FROM tb_user_log where date(in_time) between '2021-07-07' and '2021-10-31' and artical_id=0 and sign_in=1;再来计算日期对应的连续签到天数
select *,row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2 FROM (SELECT DISTINCT uid,date(in_time) dt, row_number() over (partition by uid order by date(in_time)) ranking, date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2 FROM tb_user_log where date(in_time) between '2021-07-07' and '2021-10-31' and artical_id=0 and sign_in=1)t1 ;有了连续签到天数ranking2，就可以攻克每日领取的金币数量了，取7的余数
select *,row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2, case when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=3 then 3 when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=0 then 7 else 1 end coin FROM (SELECT DISTINCT uid,date(in_time) dt, row_number() over (partition by uid order by date(in_time)) ranking, date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2 FROM tb_user_log where date(in_time) between '2021-07-07' and '2021-10-31' and artical_id=0 and sign_in=1)t1 ;
好了，按照uid，月份分组，求出对应的coin和。并且按照月份、ID升序排序
select t2.uid,DATE_FORMAT(t2.dt,'%Y%m') month,sum(t2.coin) coin FROM (select *,row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt) ranking2, case when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=3 then 3 when row_number() over(PARTITION BY t1.uid,t1.dt2 order by t1.dt)%7=0 then 7 else 1 end coin FROM (SELECT DISTINCT uid,date(in_time) dt, row_number() over (partition by uid order by date(in_time)) ranking, date(in_time)-(row_number() over (partition by uid order by date(in_time))) dt2 FROM tb_user_log where date(in_time) between '2021-07-07' and '2021-10-31' and artical_id=0 and sign_in=1)t1 )t2 group by t2.uid,DATE_FORMAT(t2.dt,'%Y%m') order by t2.uid,DATE_FORMAT(t2.dt,'%Y%m') ;
有条件还是搬吧
投递快手等公司10个岗位
