牛客网sql练习

SQL18 分组计算练习题

题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

select gender,university,COUNT(gender) user_num,
avg(active_days_within_30) avg_active_days,
avg(question_cnt) avg_question_cnt
from user_profile
group by university,gender

SQL19 分组过滤练习题

题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

常用函数：group by ... having

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

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

题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

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

常用函数：if

select 
if(age>=25,'25岁及以上','25岁以下') as age_cut,count(device_id) as number
from user_profile
group by age_cut

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

题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）

常用函数：case...when...end

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

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

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

常用函数：SUNSTR/substring、DAY、YEAR、MONTH

示例：question_practice_detail

根据示例，你的查询应返回以下结果：

select
day(date) as day,count(question_id) as question_cnt
from question_practice_detail
where substring(date,1,7) = '2021-08'
group by day(date);
 
#或者 like '2021-08%'、
#或者YEAR(date) = 2021 AND MONTH(date) = 8

SQL30 统计每种性别的人数

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

常用函数：SUNSTR

示例：user_submit

根据示例，你的查询应返回以下结果：

 1、LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；

2、POSITION(substr  IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；

3、LEFT(str, length)：从左边开始截取str，length是截取的长度；

4、RIGHT(str, length)：从右边开始截取str，length是截取的长度；

5、SUBSTRING_INDEX(str  ,substr  ,n)：截取扫描。比如n大于0，就从左往右找到第n个substr ，然后截取左边的；如果n小于0，就从右往左，找到第n个substr，截取右边的。

6、SUBSTRING(str  ,n ,m)：返回字符串str从第n个字符截取到第m个字符；

7、REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；

8、LENGTH(str)：计算字符串str的长度。

select substring_index(profile,',',-1) as gender,count(device_id)
from user_submit
group by gender