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

连续签到领金币

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

with
    t2 as (
        select
            uid,
            time,
            time - rank() over (
                partition by
                    uid
                order by
                    time
            ) rk
        from
            (
                select
                    uid,
                    date (in_time) as time
                from
                    tb_user_log
                where
                    artical_id = 0
                    and sign_in = 1
            ) t1
        where
            date (time) between '2021-07-07' and '2021-10-31'
    )
select
    uid,
    month,
    sum(money) as coin
from
    (
        select
            uid,
            left (time + 1 -1, 6) as month,
            (
                case
                    when rk1 = 0 then 7
                    when rk1 = 3 then 3
                    else 1
                end
            ) as money
        from
            (
                select
                    uid,
                    time,
                    row_number() over (
                        partition by
                            uid,
                            rk
                        order by
                            time
                    ) mod 7 as rk1
                from
                    t2
            ) t3
    ) t4
group by
    uid,
    month
order by
    month,
    uid

全部评论

相关推荐

屌丝逆袭咸鱼计划:心态摆好,man,晚点找早点找到最后都是为了提升自己好进正职,努力提升自己才是最关键的😤难道说现在找不到找的太晚了就炸了可以鸡鸡了吗😤早实习晚实习不都是为了以后多积累,大四学长有的秋招进的也不妨碍有的春招进,人生就这样
点赞 评论 收藏
分享
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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