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

连续签到领金币

https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f

#小白另类解法,好理解,请大牛多多指教
with tiaojian as (
select
uid,
artical_id,
date(in_time) as day,
lead(date(in_time),1)over(partition by uid order by date(in_time)) as pm
from tb_user_log
where
artical_id=0
and 
date(in_time) between "2021-07-07" and "2021-10-31"
and
sign_in=1
)

select 
t1.uid,
date_format(t1.day,"%Y%m"),
sum(case when pct%7=3 then 3 when pct%7=0 then 7 else 1 end)
from(
select 
t.uid,
t.day,
dense_rank()over(partition by t.uid,t.pt order by t.day) as pct
from(
select 
uid,
day,
sum(case when datediff(pm,day)=1 then 0 else 1 end)over(partition by uid order by day desc) as pt
from tiaojian
) as t 
) as t1 
group by date_format(t1.day,"%Y%m"),t1.uid
order by date_format(t1.day,"%Y%m"),t1.uid

全部评论

相关推荐

07-07 14:30
复旦大学 Java
遇到这种人我也不知道说啥了
无能的丈夫:但我觉得这个hr语气没什么问题啊(没有恶意
点赞 评论 收藏
分享
点赞 评论 收藏
分享
fRank1e:吓得我不敢去外包了,但是目前也只有外包这一个实习,我还要继续去吗
点赞 评论 收藏
分享
牛客38347925...:9,2学生暑期实习失利开始投小厂,给这群人整自信了
点赞 评论 收藏
分享
评论
1
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务