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

select u.device_id,u.university,
case when q.question_cnt is null then 0
else  q.question_cnt end,
case when r.right_question_cnt  is null then 0
else r.right_question_cnt end 
from user_profile u  
left join
(select device_id,max(rn) as question_cnt from 
(select device_id,row_number() over (partition by device_id) as rn 
from question_practice_detail where  date between"2021-08-01" and  "2021-09-01" )n group by device_id) q 
on q.device_id=u.device_id 
left join
(select device_id,max(rn) as right_question_cnt from 
(select device_id,row_number() over (partition by device_id) as rn 
from question_practice_detail where result ='right' and date between"2021-08-01" and  "2021-09-01" )m group by device_id) r
on r.device_id=u.device_id 
where u.university='复旦大学'