题解 | 统计复旦用户8月练题情况

select device_id,university,question_cnt,right_question_cnt
from(
 select up.device_id,university,count(question_id) over    (partition by device_id) as question_cnt,
 count(if(result='right',1,null)) over(partition by device_id)  as right_question_cnt,
 row_number() over (partition by device_id) as rn
 from user_profile up
 left join question_practice_detail qpd on qpd.device_id=up.  device_id
where university='复旦大学' and (month(date)=8 or date is null)
) as fudan
where rn=1