题解 | #连续签到领金币#
连续签到领金币
https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
思路是：求出签到的天数用dense_rank来进行排序，第二是用dense_rank来进行case when的签到，最后用临时表进行连接，
用临时表作为驱动表来sum()签到金币求和。得到
with a as(
select uid,
date(in_time) dt,
DENSE_RANK()over(partition by uid order by date(in_time)) bh
from tb_user_log
where date(in_time) between '2021-07-07' and '2021-11-01' and artical_id=0 and sign_in=1
),b as (
select *,
case DENSE_RANK()over(partition by date_sub(dt,INTERVAL bh day),uid order by dt)%7
when 3 then 3
when 0 then 7
else 1 end
mt_day
from a
)
select uid,DATE_FORMAT(dt,"%Y%m") as month
,sum(mt_day) coin
from b
group by uid,month
order by month,uid
查看21道真题和解析
