题解 | #连续签到领金币#
连续签到领金币
https://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
select uid, date_format(dt,"%Y%m") as month_, sum(have_money) as coin from ( select uid, dt, case when rn%7 = 3 then 3 when rn%7 = 0 then 7 else 1 end as have_money from ( select uid, dt, dense_rank()over(partition by cut_dt order by dt) as rn from ( select uid, dt, date_sub(dt, interval cut day) as cut_dt from ( select uid, dt, dense_rank()over(partition by uid order by dt) as cut from ( select distinct uid, date(in_time) as dt from tb_user_log where date(in_time) between "2021-07-07" and "2021-10-31" and artical_id = 0 and sign_in = 1 order by uid,dt ) as tb_1 ) as tb_2 ) as tb_3 ) as tb_4 ) as tb_5 group by uid,month_ order by month_,uid