#1.思路 先求出每个用户每天做的题数的具体的author_id用户ID,at.author_level用户等级,ab.answer_date,目的，
#在求连续天数的时候，确保每个用户都可以满足当天做题大于1的条件
select
ab.author_id,
at.author_level,
ab.answer_date
from answer_tb ab left join author_tb at 
on ab.author_id=at.author_id
group by ab.answer_date,ab.author_id,at.author_level
having count(issue_id)>=1
#2.通过窗口函数，求每个人的时间与每个人的时间排序差求出时间，如果求出来的时间相同，则是连续的时间，不同则不是
select
t.author_id,
t.author_level,
subdate(t.answer_date,t.m),#求出时间差
count(t.author_id)over(partition by t.author_id,subdate(t.answer_date,t.m)) as pt 
#此窗口函数，求出每个人，连续的天数，需要用窗口函数count()+partition by 用户，加时间差
from(
select
ab.author_id,
at.author_level,
ab.answer_date,
row_number()over(partition by ab.author_id order by ab.answer_date asc) as m 
from answer_tb ab left join author_tb at 
on ab.author_id=at.author_id
group by ab.answer_date,ab.author_id,at.author_level
having count(issue_id)>=1
) as t 
) as t1
---------------------------------------------------------------------------------------------------
#3.最后求用 group by 分组 求出最大的连续天数
select
t1.author_id,
t1.author_level,
max(t1.pt)
from(
select
t.author_id,
t.author_level,
count(t.author_id)over(partition by t.author_id,subdate(t.answer_date,t.m)) as pt 
from(
select
ab.author_id,
at.author_level,
ab.answer_date,
row_number()over(partition by ab.author_id order by ab.answer_date asc) as m 
from answer_tb ab left join author_tb at 
on ab.author_id=at.author_id
group by ab.answer_date,ab.author_id,at.author_level
having count(issue_id)>=1
) as t 
) as t1
where 
t1.pt>=3
group by t1.author_id,t1.pt,t1.author_level
order by t1.author_id