题解 | #某乎问答最大连续回答问题天数大于等于3天#

#请你统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序）
#根据id 将天数相加 并join上等级表
select author_id,author_level,sum(ld_cnt)
from
(#计算每个id 连续日期大于等于3的分类 计数
select author_id,count(ld) ld_cnt
from
(#用日期减去排序，可得到各日期的分类归属。当日期连续时 会得到同一标签
select author_id,answer_date-dense_rank()over(partition by author_id order by answer_date) ld
from 
(#列出每个id的活动日期表（去重）
select distinct author_id,answer_date
      from answer_tb
      join author_tb using(author_id))data1)data2
group by author_id,ld
having ld_cnt>2)data3 join author_tb using(author_id)
group by author_id,author_level