题解 | #查找除复旦大学的用户信息#

代码1：

select
c.difficult_level,
count(if(result='right',a.question_id,null)) / count(a.question_id) as correct_rate
from

(select device_id,question_id,result
from question_practice_detail
)a

join

(select device_id
from user_profile
where university = '浙江大学'
)b
on a.device_id = b.device_id


left join
(
select
question_id,difficult_level
from question_detail
)c

on a.question_id = c.question_id
group by difficult_level
order by correct_rate

代码2：

select difficult_level,
(count(case when result='right' then 1 else null end)/count(*)) as correct_rate
from user_profile 

up left join 
question_practice_detail qpd on up.device_id=qpd.device_id

left join
question_detail qd on qpd.question_id=qd.question_id

where university='浙江大学'
group by difficult_level
having difficult_level!='None'
//输出结果不能由none
order by correct_rate