题解 | 任意两个连续自然月练题次数大于1的用户

select distinct device_id from
(select device_id,year1,month1,lag(month1)over(partition by device_id order by year1,month1) month2,lag(year1)over(partition by device_id order by year1,month1) year2 from
(select device_id,year(event_date) year1,month(event_date) month1,count(question_id) cnt from question_practice_detail
group by device_id,year(event_date),month(event_date)
having cnt>1) as newtable) as new
where (month1-month2=1 and year1=year2) or (month1-month2=-11 and year1-year2=1)
order by device_id desc

有两种情况会是连续的月份：1、本年内的两个月 2、12月和次年1月。

(month1-month2=1 and year1=year2) or (month1-month2=-11 and year1-year2=1)

month1为用month（）取出的当前日期月份，month2是用lag（）over（）窗口函数取出的上一个做题数大于1的月份。

year同理