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

用到的知识点：lead窗口函数，date_sub日期函数，case when end语句，取余是%，向下取整floor函数，group/having/order可以用别名

step1:统计每个用户在2021年7月7日到2021年10月31日的的登录情况，并按照登录日期排序。

with t1 as(

select distinct uid

,date(in_time) as in_day

,date_format(in_time,"%Y%m") as in_month

,row_number() over(partition by uid order by date(in_time)) as irank

from tb_user_log

where sign_in = 1 and artical_id = 0

and date(in_time) between '2021-07-07' and '2021-10-31')

解释：

1️⃣distinct保证（uid，in_day，in_month，irank）四个的组合是唯一的，保证一个用户在一天只有一条记录

2️⃣在这种情况下使用row_number/rank/dense_rank是完全一样的，因为每条记录都不同；

3️⃣in_month字段存在是为了帮助我们取出每个月签到的金币。

step2:统计每个用户在每个月的连续登录情况

with t2 as(

select uid,in_month, date_sub(in_day, interval irank day) as temp

,count(*) as days

from t1

group by uid, temp, in_month)

解释：

1️⃣连续登录判断依据：date_sub(in_day, interval irank day)是相同的，这也就是我们在第一步对登录日期进行排序➕第二步按照辅助列temp进行分组的原因

step3:统计每个用户在每个月获得金币的情况

select uid,in_month,

sum(case

when days%7 in (1,2) then floor(days/7)*15 + days%7

when days%7 in(3,4,5,6) then floor(days/7)*15 + days%7 + 2

else floor(days/7)*15 end) as coin

from t2

group by uid, in_month

order by in_month,uid

解释：

1️⃣在第二步已经统计出每个月的连续登录天数时，我们就可以按照金币获得规则算出每个用户每个月获得金币了。

使用case when语句，对连续登录天数days%7进行判断（⚠️不是对days进行判断，因为七天一个循环）

2️⃣计算金币时也要额外小心，连续登录7天获得15个金币，所以首先是floor(days/7)*15；其次根据days%7的不同进入不同的分支。

完整代码如下：