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

select
  au.author_id,
  au.author_level,
  M.num
from
  (
    select
      a1.author_id,
      count(1) num
    from
(
        select
          a.answer_date,
          a.author_id,
          dense_rank() over(
            PARTITION BY a.author_id
            order by
              a.answer_date
          ) as t
        from
          answer_tb a
        group by
          author_id,
          answer_date
      ) a1
    group by
      date_sub(a1.answer_date, INTERVAL t day), #我就卡在了此处分组。。。日期减日期数量相等即可
      a1.author_id
    having
      count(1) >= 3
  ) M
  inner join author_tb au on au.author_id = M.author_id
order by
  au.author_id