SQL34-统计复旦用户8月练题情况.md

SQL34 统计复旦用户8月练题情况

困难  通过率：48.26%  时间限制：1秒  空间限制：256M

描述

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

示例：用户信息表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
……

根据示例，你的查询应返回以下结果：

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

答案

select
  u.device_id, university,
  count(question_id) as question_cnt,
  count(q.result) filter (where q.result='right') as right_question_cnt
from
  (select device_id, question_id, qin.result
    from question_practice_detail as qin
    where extract(month from qin.date) = 08
  ) as q
right join (select device_id, university from user_profile where university = '复旦大学') as u 
on u.device_id = q.device_id
group by u.device_id, university

Note: 对于MySQL, 我们要将 count(q.result) filter (where q.result='right') 替换为 sum(q.result = 'right')。

思路

1. 我们一定是要 join 两张表。✅

2. 既然是只要8月份的、复旦大学的用户数据，那么我们在join两张表的时候，question_practice_detail 可以只拿出8月份的数据来join，user_profile 可以只拿出复旦大学的数据来join。另外也不必选择不需要的column。✅

3. 有的用户可能一道题都没做，所以 question_practice_detail 里面没有他，而他却是复旦大学实实在在的学生，user_profile里面不会漏掉他。由此判断，join表的时候device_id要根据user_profile的来。这就是为什么要right join的原因。✅

4. join两张表之后，我们以 (device_id, university) 的pair为单位去group by就可以了，用来 count 每一个 pair 的总答题数目以及回答正确的数目。✅

5. 通过这道题我们要注意，count 函数是不去重的。尽管用户 3214 只答了112和113两道题，但是113题目答了两遍，所以我们仍 count 为 3，而这也符合题目要求。✅