题解 | 最大连续回答问题天数大于等于3天的用户及其等级

select t2.author_id,author_level,max(num) days_cnt from 
(
select author_id,count(*) num from 
(select distinct author_id,answer_date,dense_rank()over(partition by author_id order by answer_date) rk1 from answer_tb) as t1
group by author_id,date_sub(answer_date,interval rk1 day)
) as t2 left join author_tb using(author_id)
group by t2.author_id,author_level
having days_cnt>=3
order by t2.author_id
 ;

连续天数问题：

主要思路：根据用户分组，日期排序，得到序号1——根据用户和原日期与序号的差值分组并计数——最大连续天数

1.因存在用户同一天多次回答的可能，故排序时根据用户id分组、日期排序、使用dense_rank（）得到序号1--rk1，并在最前面使用distinct去重

select distinct author_id,answer_date,dense_rank()over(partition by author_id order by answer_date) rk1 from answer_tb

2.count()函数计算连续天数：

select author_id,count(*) num from

(select distinct author_id,answer_date,dense_rank()over(partition by author_id order by answer_date) rk1 from answer_tb) as t1

group by author_id,date_sub(answer_date,interval rk1 day)

此处根据用户id及原日期和序号的差值进行分组，如果是连续日期，得到的差值相同

3.求解每个用户的最大连续天数，且保留最大连续天数大于等于3的用户信息：

select t2.author_id,author_level,max(num) days_cnt from

(

select author_id,count(*) num from

(select distinct author_id,answer_date,dense_rank()over(partition by author_id order by answer_date) rk1 from answer_tb) as t1

group by author_id,date_sub(answer_date,interval rk1 day)

) as t2 left join author_tb using(author_id) #将表2与用户信息表连接得到用户等级

group by t2.author_id,author_level #根据用户id及等级分组

having days_cnt>=3 #筛选最大连续做题天数大于等于3的用户

order by t2.author_id

;