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

  
    select
    u.device_id
    ,u.university
    ,count(q.question_id) question_cnt 
    ,sum(if(result='right',1,0)) right_question_cnt
    from user_profile u
    left join question_practice_detail q 
    on u.device_id=q.device_id 
    where u.university='复旦大学'
    and substring(q.date,1,7)='2021-08'
    group by 1,2