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

# 筛选artical_id = 0 ，
# 时间筛选：20210707 - 20211031
# 字段：uid、month、coin
# 连续签到 构造   核心：排序编号 与 签到日期的差值是相等的
# V1
# with table1 as(
#     select uid,date_format(in_time,'%Y%m%d') as in_date,
#     dense_rank()over(partition by uid order by in_time) as rank_number
#     from tb_user_log
#     where in_time >='2021-07-07' and in_time <"2021-11_01" and artical_id = 0 and   
#     sign_in = 1
# ),
# table2 as(# 核心：排序编号 与 签到日期的差值是相等的
# select *,
# date_sub(in_date,interval rank_number day) as temp_df,
# # 连续签到: 通过 uid 和 temp_df(排序编号和签到日期之差) 分组 排序获得连续签到天数
# dense_rank()over(partition by uid,date_sub(in_date,interval rank_number day) order by in_date) as con_day
# from table1
# ),
# table3 as (
#     select *,
#     case when con_day % 7 = 3 then 3
#         when con_day % 7 = 0 then 7
#         else 1 end as coin_everyday
#     from table2
# )
# select uid,date_format(in_date,'%Y%m') as month,sum(coin_everyday)
# from table3
# group by uid,month
# order by month,uid

# 筛选artical_id = 0 ，
# 时间筛选：20210707 - 20211031
# 字段：uid、month、coin
# 连续签到 构造   核心：排序编号 与 签到日期的差值是相等的
# V2
# tb1：过程表,p-date签到明细
with tb1 as(
    select uid,
    date_format(in_time,"%Y%m%d") as in_date,
    date(in_time) as date,
    # 排序
    dense_rank()over(partition by uid order by date_format(in_time,"%Y%m%d")) as rk
    from tb_user_log
    where artical_id = 0 AND sign_in = 1 
    AND in_time between '2021-07-07' and '2021-11-01'
),
# tb2：判断是否连续签到
tb2 as(
    select uid,in_date,
    # 当日期和排序编号rk 之差 为定值则说明还在连续状态
    date_sub(date,interval rk day) as temp_var
    from tb1
),
# tb3：判断连续签到状态(按照uid、temp_var分组，时间排序)
tb3 as(
    select uid,in_date,
    dense_rank()over(partition by uid,temp_var order by in_date) as rk_tocoin
    from tb2
),
# tb4：结果查询：生成金币字段
tb4 as(
    select uid,in_date,rk_tocoin,date_format(in_date,"%Y%m") as month, 
    case when rk_tocoin % 7 = 3 then 3 
        when rk_tocoin % 7 = 0 then 7 else 1 
        end as coin_num
    from tb3
)
# 终表
select uid,month,sum(coin_num) as coin 
from tb4
group by uid,month
order by month,uid

# 难绷，这题第三个用例有个bug，包好了20211101这一天。。
# 总结：运用了dense_rank，第一个dense_rank获取常规排名(uid分组，date排序)得到rk
然后利用date_sub将date和rk作差
第二处dense_rank获取连续登陆天数，uid和差分组，date排序，得到连续登陆的天数...