题解 | #连续签到领金币#
连续签到领金币
https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
select uid, date_format(dt1,'%Y%m'), sum((floor(连续签到天数/8)*15 + case when mod(连续签到天数,7)>=3 and mod(连续签到天数,7)<7 then mod(连续签到天数,7)+2
else mod(连续签到天数,7)
end )) coin
from
(select uid, date_sub(dt, interval rn-1 day) dt1, count(*) 连续签到天数
from
(select uid, min(date(in_time)) dt -- 用户登陆&签到 日活跃 数据集
,row_number() over(partition by uid order by min(date(in_time))) rn
from tb_user_log
where artical_id = 0 and sign_in = 1 and date(in_time)>=date('2021-07-07') and date(in_time)<date('2021-11-01')
group by uid, date(in_time)
) a
group by uid, date_sub(dt, interval rn-1 day)
) b
group by uid, date_format(dt1,'%Y%m')
查看5道真题和解析
