1)建立子查询:先关联author_tb表,获取用户的author_level字段,并对用户每天的回答进行去重排序。
SELECT DISTINCT author_id,answer_date,author_level,DENSE_RANK()OVER(PARTITION BY author_id ORDER BY answer_date)rk FROM answer_tb JOIN author_tb USING(author_id)
SELECT author_id,author_level,COUNT(1) days_cnt FROM (SELECT DISTINCT author_id,answer_date,author_level,DENSE_RANK()OVER(PARTITION BY author_id ORDER BY answer_date)rk FROM answer_tb JOIN author_tb USING(author_id) )t1 GROUP BY author_id,author_level,DATE_SUB(answer_date,INTERVAL rk DAY) HAVING COUNT(1)>=3 ORDER BY author_id;