SQL基础+刷题

1.因为以前学过mysql、sqlsever、oracle，所以就在菜鸟教程看了SQL、和MySQL的基础知识点，捡起来一些基础，很全面，比我以前自己做的笔记更加整洁，然后自己又把重点遗忘的记录了下来，看过之后刷了牛客的SQL入门，下面摘录几个比较有记录意义的

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

本题注意，

1）.平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id) 来自上面信息三个表，需要联表，up与qpd用device_id连接并限定大学，qd与qpd用question_id连接。

select up.university,difficult_level,
round(count(qpd.question_id)/count(distinct qpd.device_id),4) as avg_answer_cnt
from question_practice_detail qpd
join user_profile up on qpd.device_id=up.device_id
join question_detail qd on qpd.question_id=qd.question_id
group by difficult_level,university
having up.university='山东大学'

2.SQL17 计算男生人数以及平均GPA

这个一开始把男生这个条件放前面筛选了，但是很麻烦，后面可以发现可以放在后面

注意：小数点取法

select count(gender) as male_mun,
round(avg(gpa),1) as avg_gpa
from user_profile where gender='male'

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

这个用到了union all ，不能用 where university = "山东大学"  or gender = "male"  ，因为结果是去重的，题目要求不去重

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

 case语句，按从上到下的书写顺序计算每个WHEN子句的布尔表达式。返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。如果没有取值为TRUE的布尔表达式，则当指定了ELSE子句时,返回ELSE子句中指定的结果；如果没有指定ELSE子句，则返回NULL。

select case when age<25 or age is null then '25岁以下'
            when age>=25 then '25岁以上'
            end age_cut,count(*) number
from user_profile
group by age_cut

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

注意，这个不用分组函数，因为只是输出划分范围了，注意输出顺序

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

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

 用year/month函数的year(date)=2021 and month(date)=8转换

用date_format函数的date_format(date, "%Y-%m")="202108"

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

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

现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

让表自链接，限定条件天数相隔为1， 用datediff函数

SELECT COUNT(distinct q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1 left outer join question_practice_detail as q2
on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1

SQL30 统计每种性别的人数

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

解：用substring_index函数进行分解

SUBSTRING_INDEX(str, delim, count)

• str：要被截取的原始字符串。
• delim：指定的分隔符。
• count：指定的出现次数，用于确定返回的子串是在分隔符之前还是之后。（1--前，-1---后）

  select substring_index(profile,',',-1) gender,
  count(*) number
  from user_submit
  group by gender

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

• 现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。
• 窗口函数：row_number，先按学校分组计算排序gpa，得到最低gpa的记录在用子查询语法拿到需要的列即可。此题中rou_number可以得到排序后的位序，取位序为1即可得到最小值（升序时）

select device_id,university,gpa
from (
    select * ,row_number() over (partition by university order by gpa) as rn from user_profile
) as un_min
where rn=1
order by university

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

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

select up.device_id, '复旦大学' as university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up

left join question_practice_detail as qpd
  on qpd.device_id = up.device_id and month(qpd.date) = 8

where up.university = '复旦大学'
group by up.device_id

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

 题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

注意：对于一个1&0序列，取平均就是1的占比 

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