题解 | 最大连续回答问题天数大于等于3天的用户及其对应等级
某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级
https://www.nowcoder.com/practice/e080f8a685bc4af3b47749ca3310f1fd
# 3.22 9:28~ 9:44 16min
# 字段：author_id，author_level，days_cnt
# 核心：连续问题的核心是 排序编号 和 日期 之差为定值则实为连续状态
# tb1：标记连续编号
with tb1 as(
select answer_date,author_id,author_level,
dense_rank()over(partition by author_id order by answer_date) as rk
from answer_tb left join author_tb using(author_id)
),
# tb2：编号与日期做差 (需要去重，distinct)
tb2 as(
select distinct answer_date,author_id,author_level, rk,
date_sub(answer_date,interval rk day) as label
from tb1
),
# tb3：计算每个用户的 连续天数
tb3 as(
select author_id,author_level,count(label) as days_cnt
from tb2
group by author_id,author_level
having count(label) >= 3
order by author_id
)
#tb4：求取其中最大的连续天数
select author_id,author_level, max(days_cnt) as days_cnt
from tb3
group by author_id,author_level
order by author_id
投递SHEIN等公司10个岗位 >
字节跳动一面208人在聊