题解 | #计算商城中2021年每月的GMV#

# 第二步： ranK()over(partition by uid order by dt) --按照签名排序 
# 第三步：date_sub(dt,interval ranK()over(partition by uid order by dt) 
#          按照签到日期减去签到排名的差值 排序，（如果是连续签到，则得到的日期相同）-- 即得到连续签到的天数，rank_day
# 第四步：rank()over(partition by uid,rank_day order by dt)，按照连续签的天数到排序，在mod与7，即可以判断连续签到的天数是否可以达到额外加金币的条件


select
             
    uid,
    date_format(dt,'%Y%m') month,
    sum(coin) coin
from (
    select
        uid,
        dt,
        case when mod(rank()over(partition by uid,rank_day order by dt), 7)=0 then 7
             when mod(rank()over(partition by uid,rank_day order by dt), 7)=3 then 3
             else 1
             end  coin 
             --   按照rank_day排序的排名，与7求mod，
    from(
        select
            uid,
            dt,
             date_sub(dt,interval ranK()over(partition by uid order by dt) day ) rank_day--  签到日期减去签到排名，（如果是连续签到，则得到的日期相同），
        from (
            select
                distinct uid,  -- 去重 过滤掉重复签到的情况
                date(in_time) dt-- 登入日期 
            from tb_user_log
            where artical_id=0 and sign_in=1
                and date(in_time)  between '2021-07-07' and '2021-10-31'
            ) t1  
          ) t2
     ) t3
group by uid,month
order by  month,uid