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

select c.device_id
from
(select a.device_id,a.month  from
    (
    select
      device_id,
      count(*) cnt,
      month(event_date) as month
    from
      question_practice_detail
    group by
      device_id,month
    having cnt>=2
  ) a
  left join (
    select
      device_id,
      count(*)  cnt,
      month(event_date) as month
    from
      question_practice_detail
    group by
      device_id,month
    having cnt>=2
  ) b
  on b.device_id=a.device_id
  where (b.month-a.month=1 or b.month-a.month=-11) and b.device_id is not null
  ) c
group by c.device_id
  order by c.device_id desc