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

SELECT
    device_id,
    university,
    SUM(question_num) question_cnt,
    SUM(right_num) right_question_cnt
FROM
(    SELECT
        up.device_id,
        up.university,
        IF(MONTH(qpd.date) = 8 ,1,0) question_num,
        IF(result = 'right' ,1,0) right_num
    FROM user_profile up
    LEFT JOIN question_practice_detail qpd ON up.device_id = qpd.device_id
    WHERE up.university = '复旦大学' )table1
    GROUP BY device_id