Sql综合练习

21年8月份练题总数

现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果。

select
    count(distinct device_id) as did_cnt,
    count(question_id) as question_cnt
from
    question_practice_detail
where
    date like "2021-08%"

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

现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0。

select
    up.device_id,
    '复旦大学' as university,
    count(question_id) as question_cnt,
    sum(if (qpd.result = 'right', 1, 0)) as right_question_cnt
from
    user_profile as up
    left join question_practice_detail as qpd on qpd.device_id = up.device_id
    and month (qpd.date) = 8
where
    up.university = '复旦大学'
group by
    up.device_id

浙大不同难度题目的正确率

现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

select
    t3.difficult_level,
    avg(
        case
            when t1.result = 'right' then 1
            else 0
        end
    ) as correct_rate
from
    question_practice_detail t1
    left join user_profile t2 on t1.device_id = t2.device_id
    left join question_detail t3 on t1.question_id = t3.question_id
where
    t2.university = '浙江大学'
group by
    t3.difficult_level
order by
    correct_rate