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

#注意，日期从 7 月 7 日开始，而不是 7月1日开始，这个题属于典型的连续日期问题。如果从没接触过建议网上先找个简单的题屡屡

#--从2021年7月7日0点开始
#-- 每天签到 领 1 金币
#-- 连续签第三天，多领 2金币
#-- 连续签到第7天 ，多领 6金币

#-- 问题，从 2021-7 ~ 2021-10 个用户每月金币数
select
    f.uid,date_format(f.time,'%Y%m') as month,sum(coinNum) as coin
from 
(
    select
        e.uid,
        e.time,
        case when e.rk % 7 = 0 then 7 else if(e.rk % 7 = 3,3,1) end as coinNum #计算该用户该天金币数量
    from 
    (
        select
            d.uid,
            d.time,
            row_number() over(partition by d.uid,d.groupId order by d.time) as rk #该记录位于该分组的位置
        from 
        (
            select
                c.uid,
                c.time,
                sum(c.flag) over(partition by c.uid order by c.time)  as groupId #各个用户不同分组的分组id
            from 
            (
                select
                    b.uid,
                    b.time,
                    case when date_sub(b.time,interval 1 day) = lag(b.time,1,'1970-01-01')  over(partition by b.uid order by b.time) 
                    then 0 else 1 end as flag
                from 
                (
                    select
                        distinct a.uid,date_format(a.in_time,'%Y-%m-%d') as time
                    from tb_user_log a 
                        where date_format(a.in_time,'%Y-%m-%d') >= '2021-07-07' and date_format(a.in_time,'%Y-%m-%d') <= '2021-10-31' 
                            and a.artical_id = 0 and a.sign_in = 1
                ) b 

            ) c 
        ) d
    ) e
) f group by f.uid,date_format(f.time,'%Y%m')

order by date_format(f.time,'%Y%m'),f.uid

###########这是2023、6，19的代码，一遍过
select 
    uid,date_format(sign_date,'%Y%m') as month,sum(coins) as coin
from 
(
    select 
        uid,sign_date,
        case when rk mod 7 = 0 then 7 
            when rk mod 7 = 3 then 3 else 1 end as coins
    from 
    (
        select 
            uid,sign_date,groupId,row_number() over(partition by uid,groupId order by sign_date) as rk
        from 
        (
            select 
                uid,sign_date,sum(flag) over(partition by uid order by sign_date) as groupId
            from 
            (
                select 
                    uid,
                    sign_date,
                    case when date_sub(sign_date,interval 1 day) = (lag(sign_date,1,'9999-12-31') over(partition by uid order by sign_date)) then 0 else 1 end as flag
                from 
                (
                    select 
                        distinct uid,date_format(in_time,'%Y-%m-%d') as sign_date
                    from tb_user_log where in_time >= '2021-07-07 10:00:00' and in_time <= '2021-11-01 10:00:00' and artical_id=0 and sign_in = 1
                ) t1
            ) t2 

        ) t3
    ) t4
) t5 group by uid,date_format(sign_date,'%Y%m')
order by `month`,uid