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

# 计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。结果按月份、ID升序排序

select
    uid,
    date_format (dt, '%Y%m') month,
    # 用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币
    sum(
        case
            when coin = 3 then 3
            when coin = 0 then 7
            else 1
        end
    ) coin
from
    (
        select
            uid,
            dt,
            mod(
                # 连续签到的第几天
                rank() over (
                    partition by
                        uid,
                        start_day
                    order by
                        dt
                ),
                7
            ) coin
        from
            (
                select
                    uid,
                    dt,
                    # 连续签到的开始时间
                    dt - r start_day
                from
                    (
                        # 签到的第几天
                        select distinct
                            uid,
                            date (in_time) dt,
                            rank() over (
                                partition by
                                    uid
                                order by
                                    date (in_time)
                            ) r
                        from
                            tb_user_log
                        where
                            date (in_time) between '2021-07-07' and '2021-10-31' 
                             # 只有artical_id为0时sign_in值才有效
                            and sign_in = 1
                            and artical_id = 0
                    ) tb_sign
            ) tb_start
    ) tb_value
group by
    uid,
    date_format (dt, '%Y%m')