描述:
题目:运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数,请取出相应数据:
用户信息表:user_profile
id | device_id | gender | age | university | gpa | active_days_within_30 | question_cnt | answer_cnt |
1 | 2138 | male | 21 | 北京大学 | 3.4 | 7 | 2 | 12 |
2 | 3214 | male | NULL | 复旦大学 | 4 | 15 | 5 | 25 |
3 | 6543 | female | 20 | 北京大学 | 3.2 | 12 | 3 | 30 |
4 | 2315 | female | 23 | 浙江大学 | 3.6 | 5 | 1 | 2 |
5 | 5432 | male | 25 | 山东大学 | 3.8 | 20 | 15 | 70 |
6 | 2131 | male | 28 | 山东大学 | 3.3 | 15 | 7 | 13 |
7 | 4321 | male | 28 | 复旦大学 | 3.6 | 9 | 6 | 52 |
第一行表示:id为1的用户的常用信息为使用的设备id为2138,性别为男,年龄21岁,北京大学,gpa为3.4,在过去的30天里面活跃了7天,发帖数量为2,回答数量为12
最后一行表示:id为7的用户的常用信息为使用的设备id为432,性别为男,年龄28岁,复旦大学,gpa为3.6,在过去的30天里面活跃了9天,发帖数量为6,回答数量为52
题库练习明细表:question_practice_detail
id | device_id | question_id | result |
1 | 2138 | 111 | wrong |
2 | 3214 | 112 | wrong |
3 | 3214 | 113 | wrong |
4 | 6534 | 111 | right |
5 | 2315 | 115 | right |
6 | 2315 | 116 | right |
7 | 2315 | 117 | wrong |
8 | 5432 | 117 | wrong |
9 | 5432 | 112 | wrong |
10 | 2131 | 113 | right |
11 | 5432 | 113 | wrong |
12 | 2315 | 115 | right |
13 | 2315 | 116 | right |
14 | 2315 | 117 | wrong |
15 | 5432 | 117 | wrong |
16 | 5432 | 112 | wrong |
17 | 2131 | 113 | right |
18 | 5432 | 113 | wrong |
19 | 2315 | 117 | wrong |
20 | 5432 | 117 | wrong |
21 | 5432 | 112 | wrong |
22 | 2131 | 113 | right |
23 | 5432 | 113 | wrong |
第一行表示:id为1的用户的常用信息为使用的设备id为2138,在question_id为111的题目上,回答错误
......
最后一行表示:id为23的用户的常用信息为使用的设备id为5432,在question_id为113的题目上,回答错误
表:question_detail
id | question_id | difficult_level |
1 | 111 | hard |
2 | 112 | medium |
3 | 113 | easy |
4 | 115 | easy |
5 | 116 | medium |
6 | 117 | easy |
第一行表示: 题目id为111的难度为hard
....
第一行表示: 题目id为117的难度为easy
请你写一个SQL查询,计算山东、不同难度的用户平均答题量,根据示例,你的查询应返回以下结果(结果在小数点位数保留4位,4位之后四舍五入):
答案如下:
university | difficult_level | avg_answer_cnt |
山东大学 | easy | 4.5000 |
山东大学 | medium | 3.0000 |
解题思路:
计算山东、不同难度的用户平均答题量,
山东大学 university ="山东大学"
不同难度 group by difficult_level
用户平均答题数 总答题数 / 总答题人数
count(device_id) / count( dinstinct device_id)
表中有用信息:
第一张表: device_id 用于关联其他表
university 用于筛选山东大学
第二张表: device_id 用于关联其他表
question_id 用于关联其他表
第三张表: question_id 用于关联其他表
difficult_level 根据难度分组
最终SQl:
select
university,
difficult_level,
count(q.device_id) / count(distinct q.device_id) avg_answer_cnt
from
user_profile u
join question_practice_detail q
join question_detail qd on u.device_id = q.device_id
and q.question_id = qd.question_id
where
u.university = "山东大学"
group by
difficult_level;