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

select
    t.device_id,
    t.university,
    COUNT(t.question_id) question_cnt,
    SUM(t.right_question_cnt) right_question_cnt
FROM
    (
        select
            t1.device_id,
            t1.university,
            t2.question_id,
            case
                WHEN result = 'right' THEN 1
                ELSE 0
            end as right_question_cnt
        from
            user_profile t1
            LEFT JOIN question_practice_detail t2 ON t1.device_id = t2.device_id
            AND t2.date BETWEEN '2021-08-01' AND '2021-08-31'
        WHERE
            t1.university = '复旦大学'
    ) t
GROUP BY
    t.device_id,
    t.university