题解 | #连续签到领金币#
连续签到领金币
https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
select uid,concat('2021',m) as "month", sum(coin_m) as "coin"
from (
select uid,count(1),m,case when count(1) >=7 then (floor(count(1)/7)*15 + (case when (count(1)%7)>=3 then count(1)%7+2 else count(1)%7 end))
when count(1) >=3 then count(1)+2 else count(1) end "coin_m"
from(
select uid,in_time,m,(day(in_time) - ranking) as "const_d",sign_in
from(
select uid,in_time,lpad(month(in_time),2,0) as "m",row_number()over(partition by uid order by in_time) as "ranking",sign_in
from tb_user_log
where in_time between '2021-07-07 00:00:00' and '2021-10-31 23:59:59'
and artical_id = 0 and sign_in = 1
)t1
)t2
group by uid,const_d,m
)t3
group by uid,m
order by month,uid
查看19道真题和解析