题解 | #连续签到领金币#
连续签到领金币
https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
首先我们将一个大问题拆分成几个小问题
1.对用户分组 获取出 7月到 10月末的所有签到信息 注意distince 去重，以及是否连续问题
dt2 处 目的：为了后面计算获取金币
dt2 思路： 当前时间减去 排序好的时间 如果是连续时间 那么差值一定相同 若差值不相同则标识签到不连续
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
2.通过上面排序后的我们可以计算 每一天对应获得的金币数 规则 连续两天 第三天额外给 2个金币也就是第三天给3个 连续6天 第七天额外给6个金币 也就是第七天给7个金币
通过 case 和取余方式 计算获取金币数
select *,
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
3 按照 uid 分组 sum求和 获取金币数
select t2.uid,DATE_FORMAT(t2.dt,'%Y%m') month, sum(coin) from (
select *,
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')
