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

with cte as(
select device_id,date_format(event_date,'%Y%m') as event_month,count(*) as num
from question_practice_detail
group by device_id,event_month ),
cte1 as (
select a.device_id,a.num+b.num as cnt
from cte a left join cte b on a.device_id=b.device_id and period_diff(a.event_month,b.event_month)=1
where b.event_month is not null )
select device_id
from cte1
group by  device_id
having min(cnt)>1
order by device_id desc