题解 | #浙大不同难度题目的正确率#

--- 浙江大学学生做了哪些题目 
SELECT university, q.device_id, question_id, result 
FROM question_practice_detail q, user_profile u
WHERE u.device_id = q.device_id AND university = '浙江大学';

--- 加上难度级别信息 
SELECT university, q.device_id, q.question_id, difficult_level, result 
FROM question_practice_detail q, user_profile u, question_detail qd
WHERE u.device_id = q.device_id AND qd.question_id = q.question_id AND university = '浙江大学';

--- 不同难度题目答题的数目
SELECT difficult_level, result, count(*)
FROM question_practice_detail q, user_profile u, question_detail qd
WHERE u.device_id = q.device_id AND qd.question_id = q.question_id AND university = '浙江大学'
GROUP BY difficult_level, result;

--- 不同难度题目答题正确的数目
SELECT difficult_level, result, count(*)
FROM question_practice_detail q, user_profile u, question_detail qd
WHERE u.device_id = q.device_id AND qd.question_id = q.question_id AND university = '浙江大学'
      AND result = 'right'
GROUP BY difficult_level, result;


---尝试提交答案，发现有全错情况 
SELECT difficult_level, 
	   count(*) / 
       (
	        SELECT count(*)
			FROM question_practice_detail iq, user_profile iu, question_detail iqd
			WHERE iu.device_id = iq.device_id 
            AND iqd.question_id = iq.question_id 
            AND iu.university = '浙江大学'
            AND iqd.difficult_level = qd.difficult_level
       ) AS correct_rate
FROM question_practice_detail q, user_profile u, question_detail qd
WHERE u.device_id = q.device_id 
AND qd.question_id = q.question_id 
AND university = '浙江大学'
AND result = 'right'
GROUP BY difficult_level, result
ORDER BY correct_rate;

--- 最后答案 
SELECT difficult_level, 
	   sum(CASE WHEN result = 'right' THEN 1 ELSE 0 END)/ 
       (
	        SELECT count(*)
			FROM question_practice_detail iq, user_profile iu, question_detail iqd
			WHERE iu.device_id = iq.device_id 
            AND iqd.question_id = iq.question_id 
            AND iu.university = '浙江大学'
            AND iqd.difficult_level = qd.difficult_level
       ) AS correct_rate
FROM question_detail qd
LEFT JOIN question_practice_detail q
ON qd.question_id = q.question_id 
JOIN user_profile u
ON u.device_id = q.device_id 
WHERE university = '浙江大学'
GROUP BY correct_rate;