SQL(牛客网例题2）

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

 两种条件。union all结果不去重，union结果去重。

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

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

SELECT
CASE
WHEN age<25 or age is null THEN '25岁以下'
WHEN age>=25 THEN '25岁及以上'
END age_cut,count(*) as number
FROM user_profile
group by age_cut

CASE

WHEN 情况 THEN 处理方式

END   统计结果的标题

最后要按照25岁上下的情况进行分组，才能出现两种情况。

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

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 age_cut
from user_profile

比26多了一个else分支 

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

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

        day()可以把日期只留最后的日，去掉年月。

        把月提取出来用month() ，因此提取年用year()

        DAYOFWEEK  返回日期的星期索引。WEEKDAY。前者从星期日=1开始，后者从星期一=0开始。

        也可以用date_format(date, "%Y-%m")="202108"  Y表示年，m表示月

用户的平均次日留存率 

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

所谓次日留存，指的是同一用户在当天和第二天都进行刷题。distinct去重

下表来自：SQL Date 函数

NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分。如2008-12-29
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔。(列名，interval 间隔数 日期)
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数。(日期1,日期2)最后是1减2
DATE_FORMAT()	用不同的格式显示日期/时间

SELECT
    COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM(
    select distinct device_id,date
    FROM question_practice_detail
    ) as q1
LEFT JOIN(
    select distinct device_id,date
    FROM question_practice_detail
    ) as q2
ON q1.device_id=q2.device_id
    AND q2.date=date_add(q1.date,interval 1 day)

这个表有点难。。我是参考了答案的结果。但是也可以分析一下解题思路。

公式是：（前一天登录的用户，今天仍然登录）/（前一天登录的用户）。所以基于同一客户，也就是他们的device_id是一样的，差距在date=1就可以了。

首先，次数存活率就是第一天的用户第二天还登陆APP的比例。用q1.device_id和q2.device_id分别代表第一天的用户和第二天的用户。需要DISTINCT处理。

q1是第一天的用户，q2是第二天的用户。q2中用到了表里提到的date_add函数，可以把日期间隔1天找到第二天的情况。 

30 统计每种性别的人数

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

 

SELECT 
    substring_index(profile, ',', -1) as gender,
    COUNT(device_id) as number
from user_submit
group by gender

需要按照gender分组，但是列表没有gender这一列，而在profile这一列的最后一个字符的位置。

需要用substring_index(字段, sep, n)可以将字段按照sep分隔：
(1).当n大于0时取第n个分隔符(n从1开始)之前的全部内容；
(2).当n小于0时取倒数第n个分隔符(n从-1开始)之后的全部内容；