牛客SQL刷题第一趴——非技术入门基础篇

id device_id gender age university province 1 2138 male 21 北京大学 Beijing 2 3214 male 复旦大学 Shanghai 3 6543 female 20 北京大学 Beijing 4 2315 female 23 浙江大学 ZheJiang 5 5432 male 25 山东大学 Shandong

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_id difficult_level 111 hard 112 medium 113 easy 115 easy 116 medium 117 easy

一、基础查询

标题：现在操作员要查看用户信息表中的所有数据，请取出相应的结果

题目：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据

主题：现在运营商需要检查用户来自哪些学校。请从用户信息表中取出已消除重复数据的学校数据。

现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。

题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 ‘user_infos_example’,，请你从用户信息表取出相应结果。

二、条件查询

题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校。

题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校。

题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄。

主题：现在运营商想要查看除复旦大学以外的所有用户的详细信息。请拿出相应的数据。

题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

题目：现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。

题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）

题目：现在运营商想找学校为北京大学、复旦大学、盛大的学生进行调研。请把相关数据拿出来。

题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

话题：现在运营商想要查看北京所有高校的用户信息，请拿出相应的数据。

主题：现在运营商想拿出用户信息表中用户的年龄，请拿出相应的数据，按年龄升序进行排序。

三、高级查询

题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据

题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。

话题：现在运营方想要分析每个学校不同性别的用户活跃度和发帖数量，请计算每个学校每个性别的用户数量、平均活跃天数和30天内的平均发帖数量。

用户信息表：user_profile

30天内活跃天数字段（active_days_within_30）

发帖数量字段（question_cnt）

回答数量字段（answer_cnt）

SELECT gender,university,
COUNT(device_id) AS user_num,
AVG(active_days_within_30) AS avg_active_day,
AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY gender,university;

SQL19 分组过滤练习题

话题：现在运营者想要查看每个学校用户的平均发帖和回复，寻找低活跃度的学校进行重点操作，请去掉平均发帖数不到5条的学校或平均回复数不到20条的学校。

SELECT university,AVG(question_cnt) AS avg_question_cnt,AVG(answer_cnt) AS avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg_question_cnt<5 OR avg_answer_cnt<20;

SQL20 分组排序练习题

标题：现在运营商希望查看用户在不同大学的平均发帖情况，并希望结果按照平均发帖情况按升序进行排序。请拿出相应的数据。

SELECT university,AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY university
ORDER BY avg_question_cnt;

四、多表查询

SQL21 浙江大学用户题目回答情况

select device_id,question_id,result
from question_practice_detail
where device_id=(select device_id from user_profile where university='浙江大学');

SQL22 统计每个学校的答过题的用户的平均答题数

运营商想知道每所学校用户平均回答的问题数量，请把数据拿出来。

请你写SQL查找每个学校用户的平均答题数目(说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数)根据示例，你的查询应返回以下结果（结果保留4位小数），注意：结果按照university升序排序！！！

用户信息表 user_profile，其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数。

select u.university,ROUND(count(q.question_id)/count(DISTINCT(q.device_id)),4)
from user_profile u
join question_practice_detail q
on u.device_id=q.device_id
group by 1
order by 1;

SQL23 统计每个学校各难度的用户平均刷题数

题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

select u.university,
q2.difficult_level,
ROUND(count(u.question_cnt)/count(distinct(u.device_id)),4) as avg_answer_cnt
from user_profile u
join question_practice_detail q1
on u.device_id=q1.device_id
join question_detail q2
on q1.question_id=q2.question_id
group by 1,2;

ps：用户平均答题量 = 答题总数 / 用户数，要明确答题总数需要用question_id的行数来表示，也就是需对question_id列进行计数，用户数是device_id的行数，其中答题的题目可以重复，用户则不能重复计数

SQL24 统计每个用户的平均刷题数

题目：运营商想查看山东大学用户在不同困难情况下的平均答题次数，请拿出相应的数据

请你写一个SQL查询，计算山东、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

select u.university,
q2.difficult_level,
ROUND(count(u.question_cnt)/count(DISTINCT(u.device_id)),4) as avg_answer_cnt
from user_profile u
join question_practice_detail q1
on u.device_id=q1.device_id
join question_detail q2
on q1.question_id=q2.question_id
where u.university='山东大学'
group by 1,2;

解题思路：1⃣️多表连接

2⃣️用户平均答题量 = 答题总数 / 用户数，要明确答题总数需要用question_id的行数来表示，也就是需对question_id列进行计数，用户数是device_id的行数，其中答题的题目可以重复，用户则不能重复计数

3⃣️where子句筛选university为山东大学。

4⃣️group by 按照大学分组。

SQL25 查找山东大学或者性别为男生的信息

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的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';

五、必会的常用函数

SQL26 计算25岁以上和以下的用户数量

题目：现在运营商想把用户分为25岁以下和25岁以上两个年龄段，分别检查这两个年龄段的用户数。

本题注意：age为null 也记为 25岁以下

select (case when age>=25 then '25岁及以上' else '25岁以下' end) as age_cut,
count(DISTINCT(device_id)) as number
from user_profile
group by 1;

解题思路：1⃣️case when，或者if函数都可以。

2⃣️有聚合函数要用group by。

SQL27 查看不同年龄段的用户明细

主题：现在运营商希望将用户分为三个年龄段：20岁以下、20-24岁、25岁以上。查看不同年龄段用户的详细信息。请拿出相应的数据。(注：如年龄为空，请返回其他。)

select device_id,
gender,
(case when age<20 then '20岁以下'
when age>=20 and age24 then '20-24岁'
when age>=25 then '25岁及以上'
else '其他' end) as age_cut
from user_profile;

SQL28 计算用户8月每天的练题数量

题目：现在运营商要计算2021年8月每天的用户练习题数量，请拿出相应的数据。

select DAY(date),
count(question_id) as question_cnt
from question_practice_detail
where date>='2021-08-01' and date'2021-8-31'
group by 1;

SQL29 计算用户的平均次日留存率

主题：现在运营商希望看到用户在做完一天练习后第二天回来的平均概率。请拿出相应的数据。

select avg(if(b.device_id is not null,1,0)) as avg_ret
from(select distinct device_id,date
    from question_practice_detail)a
    left join
    (select distinct device_id,date_sub(date,interval 1 day) as date
    from question_practice_detail)b
    on a.device_id=b.device_id and a.date=b.date;

SQL30 统计每种性别的人数

题目：现在运营商已经举办了一场大赛，并收到了一些参赛申请。数据记录的形式如下。现在运营商要统计的是各性别用户的参赛人数，请拿出相应的结果。

select substring(profile,15,6) as gender,count(device_id) as number
from user_submit
group by 1;

SQL31 提取博客URL中的用户名

题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

select device_id,
substring(blog_url,11,11) as user_name
from user_submit;

SQL32 截取出年龄

题目：现在运营商已经举办了一场大赛，并收到了一些参赛申请。表格数据的记录形式如下。现在运营商想要为每个年龄段的用户统计参赛者的数量。请拿出相应的结果。

select substring(profile,12,2) as age,
count(device_id) as number
from user_submit
group by 1;

SQL33 找出每个学校GPA最低的同学

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

根据示例，你的查询结果应参考以下格式，输出结果按university升序排序：

-- 方法1:join
select u.device_id,u.university,u.gpa
from user_profile u
join (select university,min(gpa) as gpa from user_profile group by university) u1
on u.university=u1.university and u.gpa=u1.gpa
order by university;

-- 方法2:select子查询
select device_id,university,gpa
from user_profile
where (university,gpa) in(select university,min(gpa) from user_profile
                       group by university)
order by university;

解题思路：也可以使用窗口函数。

六、综合练习

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

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

select u.device_id,u.university,
sum(if(question_id is not NUll,1,0)) 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 and month(date)=8
where u.university='复旦大学'
group by 1,2;

解题思路：1⃣️多表连接，使用left join

2⃣️关于日期函数写在where中会报错。

关于month(date)为什么不写在where后面：首先month函数不是聚合函数，是可以写在where语句中的；其次是，如果写在where中，是对连接好的表进行判断，如果是用user表leftjoin question表，由于question表里没有4321用户的记录，那么连接好的表中的id为4321的用户是没有date值的，也就是date为空，所以在执行where month(date)=8的时候会除掉4321这行记录，所以最后的结果里就没有这个id的记录啦！主要是要理解：1.先执行from，再执行where，where中的操作是对连接好的表的操作；2.a左连接b，对于a有而b没有的id，则连接好的表中的这些id的b相关的属性值为空。

SQL35 浙大不同难度题目的正确率

话题：现在运营商想知道浙江大学用户在不同难度题下的答题正确率，请取出相应的数据按准确度升序输出。

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

解题思路：1⃣️多表连接

2⃣️正确率的计算公式：回答正确的个数/回答的总题目

SQL39 21年8月份练题总数

题目： 现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果

select count(DISTINCT(device_id)) as did_cnt,count(question_id) as question_cnt
from question_practice_detail
where date>='2021-08-01 00:00:00' and date'2021-08-31 23:59:59';

Original: https://www.cnblogs.com/ruoli-121288/p/16378075.html
Author: 徐若离
Title: 牛客SQL刷题第一趴——非技术入门基础篇