题解 | 统计复旦用户8月练题情况
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
select
p1.device_id,
p1.university,
count(p2.question_id) as question_cnt,
sum(case when p2.result='right' then 1 else 0 end) as right_question_cnt
from(
select device_id,university
from user_profile
where university='复旦大学'
) p1
left join(
select device_id,question_id,result,date
from question_practice_detail
where year(date)=2021 and month(date)=8
) p2
on p1.device_id=p2.device_id
group by p1.device_id,p1.university
查看20道真题和解析