题解 | #连续签到领金币#
连续签到领金币
https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
#小白另类解法,好理解,请大牛多多指教 with tiaojian as ( select uid, artical_id, date(in_time) as day, lead(date(in_time),1)over(partition by uid order by date(in_time)) as pm from tb_user_log where artical_id=0 and date(in_time) between "2021-07-07" and "2021-10-31" and sign_in=1 ) select t1.uid, date_format(t1.day,"%Y%m"), sum(case when pct%7=3 then 3 when pct%7=0 then 7 else 1 end) from( select t.uid, t.day, dense_rank()over(partition by t.uid,t.pt order by t.day) as pct from( select uid, day, sum(case when datediff(pm,day)=1 then 0 else 1 end)over(partition by uid order by day desc) as pt from tiaojian ) as t ) as t1 group by date_format(t1.day,"%Y%m"),t1.uid order by date_format(t1.day,"%Y%m"),t1.uid