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

逻辑：

1-去重，对同一用户同一天多次登录只保留一次记录；根据条件初步筛选。

2-识别是否连续登录，用一个变量x进行标识。x值相同的记录，表示这几天是连续登录

3-根据x计算这一天是连续登录的第y天

4-根据y计算每一天获得多少金币

5-按照uid,month分组求和

1-去重，对同一用户同一天多次登录只保留一次记录；初步筛选，文章id=0，签到sigin_in=1，时间在20210707~20211031

将结果作为t1表

select distinct uid,
date(in_time) ymd
from tb_user_log
where artical_id = 0 and sign_in = 1 and (date(in_time) between '2021-07-07' and '2021-10-31')
order by uid, date(in_time)

2-识别连续登录，结果作为t2

select uid, ymd,
date_sub(ymd, interval row_number() over(partition by uid order by ymd) day) sub_date
from t1

逻辑：先使用row_number对同uid进行编号rm，用ymd-rm，差值为sub_date，这个值本身没有意义，只是为判断是否连续

如果时间连续sub_date值应该保持相同，如果登录时间产生跳跃，sub_date值也会发生变化。

3-根据上一步，我们使用sub_date标识是否连续登录，sub_date相同表示这几天用户是连续登录的

接下来要获取每一天是连续登录的第几天，使用row_number函数，对分区（同一uid，同一sub_date）编号num

结果作为t3

select uid, ymd,
row_number() over(partition by uid, sub_date order by ymd) num
from t2

4-计算每一天获得多少金币

逻辑：7天一个周期，根据num(该变量表示这一天是该用户连续登录的第几天)即可计算每天的金币数量

第3天满足：num%7=3 得到3金币

第7天满足：num%7=0 得到7金币

其余1金币

结果作为t4

select uid, ymd, num,
(case when num%7=3 then 3 when num%7=0 then 7 else 1 end) daily_coin
from t3

5-最后按uid和月，分组求和即可

select uid, date_format(ymd,'%Y%m') month, sum(daily_coin)
from t4
group by uid, month

以下是总代码

with t1 as(
select distinct uid,
date(in_time) ymd
from tb_user_log
where artical_id = 0 and sign_in = 1 and (date(in_time) between '2021-07-07' and '2021-10-31')
order by uid, date(in_time)
),
t2 as(
select uid, ymd,
date_sub(ymd, interval row_number() over(partition by uid order by ymd) day) sub_date
from t1),
t3 as(
select uid, ymd,
row_number() over(partition by uid,sub_date order by ymd) num
from t2
),
t4 as(
select uid, ymd, num,
(case when num%7=3 then 3 when num%7=0 then 7 else 1 end) daily_coin
from t3
)