题解 | #连续签到领金币#
连续签到领金币
http://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
select uid,month,sum((case when nums%7>=3 then 2 else 0 end)+floor(nums/7)*8+nums)as coin
from
(select uid,date_format(d,'%Y%m')as month,count(ct)as nums
from
(select d,uid,num,(d-num)as ct
from
(select distinct date(in_time)as d,uid,
row_number()over(partition by uid order by in_time)as num
from tb_user_log
where artical_id=0 and date(in_time)between '2021-07-07' and '2021-10-31' and sign_in=1)t1)t2
group by uid,month,ct)t3
group by uid,month
order by month,uid
from
(select uid,date_format(d,'%Y%m')as month,count(ct)as nums
from
(select d,uid,num,(d-num)as ct
from
(select distinct date(in_time)as d,uid,
row_number()over(partition by uid order by in_time)as num
from tb_user_log
where artical_id=0 and date(in_time)between '2021-07-07' and '2021-10-31' and sign_in=1)t1)t2
group by uid,month,ct)t3
group by uid,month
order by month,uid
