select device_id,age
from user_profile
order by age;

SQL37 查找后多列排序

题目：现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据。

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

SQL38 查找后降序排列

题目：现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据。

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

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 统计每个学校的答过题的用户的平均答题数

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岁以上和以下的用户数量

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;

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

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;

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;

题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

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';