题解 | #任意两个连续自然月练题次数大于1的用户#
任意两个连续自然月练题次数大于1的用户
https://www.nowcoder.com/practice/a4cea6942a4f4354b0a0181aa5f446d2
select distinct device_id from (select device_id, cnt, lead(cnt, 1, 0) over(partition by device_id order by month_now) as cnt_next, month_now, lead(month_now,1,month_now) over(partition by device_id order by month_now) as mon_next from (select device_id, date_format(event_date, '%Y-%m') as month_now, count(question_id) as cnt from question_practice_detail group by device_id, date_format(event_date, '%Y-%m')) as ta1 ) as ta2 where cnt >=2 and cnt_next>=2 and timestampdiff(month, concat(month_now,'-01'),concat(mon_next,'-01'))=1 order by device_id desc
