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

知识点

示例：用户信息表user_profile

id	device_id	gender	age	university	gpa	active_days_within_30
1	2138	male	21	北京大学	3.4	7
2	3214	male		复旦大学	4.0	15
3	6543	female	20	北京大学	3.2	12
4	2315	female	23	浙江大学	3.6	5
5	5432	male	25	山东大学	3.8	20
6	2131	male	28	山东大学	3.3	15
7	4321	female	26	复旦大学	3.6	9

示例：question_practice_detail

id	device_id	question_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15
……

示例： question_detail

question_id	difficult_level
111	hard
112	medium
113	easy
115	easy
116	medium
117	easy

 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

返回以下结果：

device_id	university	question_cnt	right_question_cnt
3214	复旦大学	3	0
4321	复旦大学	0	0

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

返回以下结果：

difficult_level	correct_rate
easy	0.5000
medium	1.0000