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

select
    device_id,
    university,
    COUNT(question_id) as question_cnt,
    SUM(IF(result='right',1,0)) as right_question_cnt
from
    (
        select
            UP.device_id as device_id,
            UP.university as university,
            QPD.question_id as question_id,
            QPD.result as result,
            QPD.date as date
        from
            user_profile UP
            left join question_practice_detail QPD on QPD.date like '2021-08%' and UP.device_id = QPD.device_id
        where
            UP.university = '复旦大学'
    ) tmp
group by
    device_id