题解 | #北京大学或练题数大于2的学生ID#

select distinct device_id
from 
    (select device_id,university,
	#窗口函数在一行最后标注该id的总练习数
    sum(if(question_id is not null,1,0)) over(partition by device_id)as cnt
    from user_profile
    left join question_practice_detail
    using(device_id)
    )t
where  university='北京大学' or cnt >2
order by 1 desc