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

select distinct device_id
from 
    (select *,
    lead(cnt1) over(partition by device_id order by da) as cnt2
    from 
        (select device_id,
        date_format(event_date,'%Y-%m') as da,
        count(*) as cnt1
        from question_practice_detail
        group by device_id,date_format(event_date,'%Y-%m')
        order by 1,2
        ) t1
    )t2
where cnt1 >1 and cnt2 >1
order by device_id desc