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

select device_id
from (
    select date1, device_id, num, 
            lag(date1, 1) over(partition by device_id order by date1 asc) as date2
    from (
        select substr(event_date,1,7) as date1,device_id, count(*) as num
        from question_practice_detail
        group by substr(event_date,1,7),device_id
        having count(*)>1
    ) as t
) as t1
where (substr(date1,6,2) - substr(date2,6,2) = 1) or (substr(date1,6,2) - substr(date2,6,2) = -11)
order by device_id desc
;