牛客网之SQL非技术快速入门(9)-综合练习

知识点

示例：用户信息表user_profile

示例：question_practice_detail

示例： question_detail

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

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

返回以下结果：

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

select
  c.difficult_level,
--方法1：
sum(if(b.result = 'right', 1, 0)) / count(b.result) as correct_rate
--方法2：
sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
--方法3：
count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from
  user_profile a
  join question_practice_detail b on a.device_id = b.device_id
  join question_detail c on b.question_id = c.question_id
where
  a.university = '浙江大学'
group by
  difficult_level
order by
  correct_rate asc

返回以下结果：