牛客题霸-SQL入门篇（刷题记录二）

本文基于前段时间学习总结的 MySQL 相关的查询语法，在牛客网找了相应的 MySQL 题目进行练习，以便加强对于 MySQL 查询语法的理解和应用。

以下内容是牛客题霸-SQL入门篇剩余的第 21-39 道题目的 MySQL 代码答案。

由于涉及到的数据库表较多，因此本文不再展示，只提供 MySQL 代码与示例输出。

部分题目因为较难，难以独立做出，故附上题目解法讨论的链接供大家参考。

SQL 题目

SQL 21：查询所有来自浙江大学的用户题目回答明细情况

select qpd.device_id, question_id, result
from question_practice_detail qpd
join user_profile up
on qpd.device_id = up.device_id
where up.university = '浙江大学'

SQL 22：查询每个学校用户的平均答题数目

select university, count(question_id)/count(distinct(up.device_id)) as avg_answer_cnt
from question_practice_detail qpd
join user_profile up
on qpd.device_id = up.device_id
group by university

SQL 23：查询不同学校、不同难度的用户平均答题量

select university, difficult_level, count(qpd.question_id)/count(distinct(qpd.device_id)) as avg_answer_cnt
from user_profile up
join question_practice_detail qpd
on up.device_id = qpd.device_id
join question_detail qd
on qpd.question_id = qd.question_id
group by university, difficult_level

SQL 24：查询山东大学中不同难度的用户平均答题量

from user_profile up
join question_practice_detail qpd
on up.device_id = qpd.device_id
join question_detail qd
on qpd.question_id = qd.question_id
group by university, difficult_level
having university = '山东大学'

SQL 25：查询学校为山东大学或者性别为男性的用户的 device_id、gender、age 和 gpa 数据（结果不去重）

select device_id, gender, age, gpa from user_profile
where university = "山东大学"
union all
select device_id, gender, age, gpa from user_profile 
where gender = "male"

SQL 26：查询 25 岁以下和以上的用户数量，age 为 null 也记为 25岁以下

select if(age < 25 or age is null, '25岁以下', '25岁及以上') as age_cut, 
count(*) as number
from user_profile
group by age_cut

SQL 27：查询 20 岁以下，20-24 岁，25 岁及以上三个年龄段用户的明细情况（若年龄为空请返回其他）

select device_id, gender, 
if(age < 20, '20岁以下', if(age between 20 and 24, '20-24岁', if(age >= 25, '25岁及以上', '其他'))) as age_cut
from user_profile

SQL 28：查询 2021 年 8 月每天用户练习题目的数量

select day(date) as day, count(question_id)
from question_practice_detail
where year(date) = 2021 and month(date) = 8
group by day

SQL 29：查询用户在某天刷题后第二天还会再来刷题的平均概率（平均次日留存率，难点）

select count(distinct q2.device_id, q2.date)/count(distinct q1.device_id, q1.date) as avg_ret
from question_practice_detail q1 
left join question_practice_detail q2
on q1.device_id=q2.device_id and datediff(q2.date,q1.date) = 1

链接：SQL 29 题目解法讨论

SQL 30：查询每个性别的用户分别有多少参赛者

select if(profile like '%female%', 'female', 'male') as gender, 
count(*) as number
from user_submit
group by gender

SQL 31：查询博客 URL 中的用户名

select device_id, substr(blog_url, 11) as user_name 
from user_submit

SQL 32：查询每个年龄的用户分别有多少参赛者

select substr(profile,12,2) as age, count(*)
FROM user_submit
group by age

SQL 33：查询每个学校 GPA 最低的同学

select device_id, u1.university, u1.gpa 
from user_profile u1
join(
	select university, min(gpa) as gpa from user_profile
	group by university
) u2
on u1.university = u2.university and u1.gpa = u2.gpa
order by u1.university

SQL 34：查询复旦大学的每个用户在 8 月份练习的总题目数和回答正确的题目数，对于在 8 月份没有练习过的用户，结果返回 0（难点）

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

链接：SQL 34 题目解法讨论

SQL 35：查询浙江大学的用户在不同难度题目下答题的正确率情况，并按照准确率升序输出

select difficult_level, sum(if(result = "right", 1, 0))/count(qpd.result) as correct_rate
from user_profile u
join question_practice_detail qpd
on u.device_id = qpd.device_id
join question_detail qd
on qpd.question_id = qd.question_id
where university = '浙江大学'
group by difficult_level
order by correct_rate

SQL 36：查询用户信息表中的 device_id 和 age ，并按照 age 升序排序

select device_id, age from user_profile
order by age

SQL 37：查询用户信息表中的device_id，age 和 gpa，先按照 gpa 升序排序，再按照年龄升序排序

select device_id, gpa, age from user_profile
order by gpa, age

SQL 38：查询用户信息表中的 age 和 gpa，先按照 gpa 降序排序，再按照 age 降序排序

select device_id, gpa, age from user_profile
order by gpa desc, age desc

SQL 39：查询 2021 年 8 月份所有练习过题目的总用户数和练习过题目的总次数

select count(distinct device_id) as did_cnt,
count(question_id) as question_cnt
from question_practice_detail
where year(date) = 2021 and month(date) = 8