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

select t1.author_id,author_tb.author_level,t1.days_cnt from author_tb
left join 
(SELECT 
sub.author_id,
date_sub(sub.answer_date, interval sub.paiming day) as riqi,count(distinct answer_date) as days_cnt
FROM (
    SELECT answer_date, author_id,
           ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY answer_date ASC) AS paiming
    FROM answer_tb
) AS sub
group by sub.author_id,riqi
having days_cnt >=3) as t1
on author_tb.author_id=t1.author_id 
where days_cnt>=3;