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

select
    u.device_id,u.university,ifnull(q.question_cnt,0) question_cnt,ifnull(t.right_question_cnt,0) right_question_cnt
from
    user_profile u
    left join (
        select
            device_id,
            count(id) as question_cnt
        from
            question_practice_detail
            where `date` BETWEEN '2021-08-01' AND '2021-08-31'
        group by
            device_id
    )q on u.device_id = q.device_id
    left join (
        select
            device_id,
            count(id) as right_question_cnt
        from
            question_practice_detail
            where  result = 'right' and `date` BETWEEN '2021-08-01' AND '2021-08-31'
        group by
            device_id
    ) t on u.device_id = t.device_id
    where university='复旦大学'