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刷题第一趴——非技术入门基础篇
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/622327/
转载文章受原作者版权保护。转载请注明原作者出处！