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

with 
uq22 as(
    SELECT uq1.device_id, uq1.university, uq1.question_id,uq1.result,uq1.date, 
       CASE WHEN uq1.result = 'right' THEN 1 ELSE 0 END AS right_count
    FROM
    (SELECT u.device_id, university, question_id,result,date 
    FROM user_profile u LEFT JOIN question_practice_detail q
    ON u.device_id = q.device_id AND MONTH(date) = 8
    WHERE university = '复旦大学'
    ) uq1
)


SELECT device_id, university, COUNT(question_id) question_cnt, SUM(right_count)  right_question_cnt
FROM uq22
GROUP BY device_id

小白做了好久才优化简洁了一点，这里关键在于用LEFT JOIN 时要ON u.device_id = q.device_id AND MONTH(date) = 8这样才能保证临时表uq22有那位不在8月份做题的复旦学生出现，只要这个表出来了就随便来了