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

首先按照题目要求，找出符合条件的签到用户和每个用户的日期，与签到天数排序。

select distinct uid
        , date(in_time) as dt
        , dense_rank()over(partition by uid order by date(in_time)) as num1
from tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31' and artical_id = 0 and sign_in = 1

接着，连续签到问题，要记住一个常用的点：签到日期减去排序数值，如果是连续行为，会得到同样的日期，如果不是，则表明了断签 因此，创建临时表以下： 再用排序可以记录下各个用户的连续签到天数

with c as 
(
    select a.uid,a.dt,a.num1
    , date_sub(dt,interval num1 day) as dt_test
    , dense_rank()over(partition by date_sub(dt,interval num1 day),uid order by a.dt) as continue_days
    from
    (
        select distinct uid
        , date(in_time) as dt
        , dense_rank()over(partition by uid order by date(in_time)) as num1
        from tb_user_log
        where date(in_time) between '2021-07-07' and '2021-10-31'
        and artical_id = 0 and sign_in = 1
    )a
)

最后，找到了连续天数，需要标记每天的领取金币个数，由于第三天与第七天会额外奖励，可以用case when 进行每天实际获得金币个数的统计

select d.uid, date_format(d.dt,'%Y%m') as month, sum(coin_day)as coin
from 
    (
        select uid
            ,dt
            , case when continue_days % 7 = 3 then 3
                when continue_days % 7 = 0 then 7
                else 1 end as coin_day
        from c 
    )d
group by d.uid,month
order by month,d.uid

最后可以得到每个用户每个月所得的金币个数，组合最终代码如下

with c as 
(
    select a.uid,a.dt,a.num1
    , date_sub(dt,interval num1 day) as dt_test
    , dense_rank()over(partition by date_sub(dt,interval num1 day),uid order by a.dt) as continue_days
    from
    (
        select distinct uid
        , date(in_time) as dt
        , dense_rank()over(partition by uid order by date(in_time)) as num1
        from tb_user_log
        where date(in_time) between '2021-07-07' and '2021-10-31'
        and artical_id = 0 and sign_in = 1
    )a
)
select d.uid, date_format(d.dt,'%Y%m') as month, sum(coin_day)as coin
from 
    (
        select uid
            ,dt
            , case when continue_days % 7 = 3 then 3
                when continue_days % 7 = 0 then 7
                else 1 end as coin_day
        from c 
    )d
group by d.uid,month
order by month,d.uid