2026-08-04

1 2026-08-04

1.1 Q37

select
 
    up.device_id,
 
    up.university,
 
    count(qpd.question_id) as question_cnt,
 
    sum(case when qpd.result = "right" then 1 else 0 end) as right_question_cnt
 
from
 
    user_profile as up
 
left join question_practice_detail qpd
 
    on up.device_id = qpd.device_id
 
    and qpd.date >= "2021-08-01"
 
    and qpd.date <= "2021-08-31"
 
where
 
    university = "复旦大学"
 
group by
 
    up.device_id,
 
    up.university
 
order by
 
    up.device_id

1.2 SQL38

select
 
    qd.difficult_level,
 
    round(avg(if(qpd.result = "right", 1, 0)), 4) as correct_rate
 
from
 
    user_profile as up
 
inner join question_practice_detail as qpd
 
    on up.device_id = qpd.device_id
 
inner join question_detail as qd
 
    on qpd.question_id = qd.question_id
 
where
 
    up.university = "浙江大学"
 
group by
 
    qd.difficult_level
 
order by
 
    correct_rate asc

1.3 SQL39

select
 
    count(distinct device_id) as did_cnt,
 
    count(question_id) as question_cnt
 
from
 
    question_practice_detail as qpd
 
where
 
    month(qpd.date) = 8
 
    and year(qpd.date) = 2021