题解 | #连续签到领金币#

解题思路：

1.查询用户登录日志表t1,按照签到时间排名ranking，两者相减计算每次连续签到的起始时间；

2.查询用户连续签到天数，使用窗口函数对用户和每次签到的起始时间分组，按照登录时间升序；

3.计算当日获取金币数；

4.计算用户各月份获取金币数，按照用户和月份分组求和即可；

难点：

1.用户连续登录天数判断：如果连续签到，dt-ranking的值（即连续签到的起始时间）相同；

用户当日签到排名：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

2.计算当日获取金币数：使用case函数判断，按照连续签到天数7的余数来判断：

• 当签到天数%7=3 则领取3金币
• 当签到天数%7=0 则领取7金币
• 其余情况，领取1金币

case when row_number() over(partition by uid,dt2 order by date(dt))%7=3 then 3
when row_number() over(partition by uid,dt2 order by date(dt))%7=0 then 7
else 1 end coin

完整代码如下：

select uid,date_format(dt,'%Y%m') month,sum(coin) coin
from
(select *,
row_number() over(partition by uid,dt2 order by date(dt)) ranking2,
case 
when row_number() over(partition by uid,dt2 order by date(dt))%7=3 then 3
when row_number() over(partition by uid,dt2 order by date(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,
#如果用户是连续签到，则dt-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 uid,date_format(dt,'%Y%m')
order by uid,date_format(dt,'%Y%m')
;

@骨碌圆感谢ღ( ´･ᴗ･` )比心