题解 | #连续签到领金币#
连续签到领金币
https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
-- 1.筛选出 有效数据 with t1 as ( select * from tb_user_log where in_time > '2021-07-07 00:00:00' and in_time <'2021-11-01 00:00:00' and artical_id=0 and sign_in=1 ), -- 时间减去等差数列 算连续值 t2 as( select uid,in_time, date_sub(date(in_time),INTERVAL n day) gk from ( select uid,in_time, row_number() over(partition by uid order by in_time) n from t1) t ), -- 开窗 用连续值分区 打上连续天数的序号 t3 as( select uid,in_time,DATE_FORMAT(in_time, '%Y%m%d') `month`, row_number() over(partition by uid,gk order by in_time) xh from t2 ), -- 根据序号打上额外金币数标签 t4 as( select uid,in_time,`month`, case when xh%7=0 then 6 when xh%7=3 then 2 else 0 end as ew from t3 ) -- 聚合每日金币数，和额外金币数 select uid,date_format(`month`,'%Y%m'),count(distinct `month`)+sum(ew) from t4 group by 1,2
查看30道真题和解析