连续问题解题思路

例题1:连续7日签到领金币

思路：首先，筛选出各用户符合条件的签到日期；其次，对签到表进行排序。使用窗口函数row_number()，以uid分组；按照date(in_time)排序；然后，求出各用户连续登录的天数。用dt-ranking，得到日期相同的为一组。此组数量则为连续签到天数，然后，再计算日期对应的连续登陆天数；就可以攻克每日领取的金币数量了，取7的余数

with t1 as (select uid,date(in_time) as 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')
,t2 as(select distinct(uid),dt,row_number()over(partition by uid order by dt) as ranking
from t1)
,t3 as(select uid,dt,ranking,date(dt-ranking) as dt2
from t2)
,t4 as (select *,row_number()over(partition by uid,dt2 order by dt) as ranking2
from t3)

#select * ,(case when ranking2%7=3 then 3 when ranking2%7=0 then 7 else 1 end)from t4

select uid,month,sum(coin) as coin from(
select uid,date_format(dt,'%Y%m') as month,(case when ranking2%7=3 then 3 when ranking2%7=0 then 7 else 1 end) as
coin
from t4
) a
group by uid,month

例题2:某乎问答最大连续回答问题的天数大于等于3天的用户及其等级 

思路：建立临时表t1选出每个用户的ID以及回答问题的日期；建立临时表t2，使用row_number（）函数对每个用户回答问题的日期进行排序；建立临时表t3，通过看回答问题的日期-排名是否想等判断是否连续

with t1 as(select distinct author_id,answer_date from answer_tb)
,t2 as(select *,row_number()over(partition by author_id order by answer_date) as rk
from t1)
,t3 as(select *,date_format((answer_date-rk),'%Y-%m-%d') as dt2 from t2)

select a.author_id,b.author_level,a.days_cnt from (
select author_id,count(dt2) as days_cnt from t3
group by author_id,dt2
having count(dt2)>=3) a left join author_tb b
on a.author_id=b.author_id

例题3:请你统计连续2天及以上在该店铺购物的用户及其对应的次数

思路同上

with t1 as(select distinct user_id,sales_date from sales_tb)
,t2 as(select *,row_number()over(partition by user_id order by sales_date) as rk from t1)
,t3 as(select *,(sales_date-rk) as dt2 from t2)

select user_id,count(dt2) as days_count
from t3
group by user_id,dt2
having count(dt2)>=2