8、刷牛客网SQL题(二)

自学大数据的菜鸡

已于 2022-03-27 16:33:40 修改

阅读量1.1k

点赞数

分类专栏： SQL 文章标签： sql 数据库 mysql

于 2022-03-25 14:44:59 首次发布

本文链接：https://blog.csdn.net/yunweijie_cn/article/details/123732862

版权

SQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

题目来源

牛客网SQL大厂面试题——用户增长场景（某度信息流）

一、SQL1（简单)

1.题目内容

描述
用户行为日志表tb_user_log
在这里插入图片描述
场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

问题：统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序。

输出示例：
示例数据的输出结果如下
在这里插入图片描述
解释：
11月1日有2个人浏览文章，总共浏览时长为31+24+11=66秒，人均浏览33秒；
11月2日有2个人浏览文章，总共时长为50+23=73秒，人均时长为36.5秒。

2.思路分析

题目求的是2021年11月份每天的人均浏览时长（秒数），所以可以直接按时间过滤出来2021-11的数据，然后按时间分组，分别计算总共的浏览时长和浏览的人数（人数要去重），然后相除，取一位小数并按平均时长排序即可。

3.语句实现

select
    the_day dt,
    cast(sum(the_second)/count(distinct(uid)) as decimal(16,1)) avg_viiew_len_sec
from
(
    select
        uid,
        TIMESTAMPDIFF(second,in_time,out_time) the_second,
        date_format(in_time,'%Y-%m-%d') the_day
    from tb_user_log
    where date_format(in_time,'%Y-%m')='2021-11' and artical_id!=0
)t1
group by the_day
order by avg_viiew_len_sec

二、SQL2（中等)

1.题目内容

描述
用户行为日志表tb_user_log
在这里插入图片描述
问题：统计2021年11月每天新用户的次日留存率（保留2位小数）

注：
次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。

输出示例：
示例数据的输出结果如下
在这里插入图片描述
解释：
11.01有3个用户活跃101、102、103，均为新用户，在11.02只有101、103两个又活跃了，因此11.01的次日留存率为0.67；
11.02有104一位新用户，在11.03又活跃了，因此11.02的次日留存率为1.00；
11.03有105一位新用户，在11.04未活跃，因此11.03的次日留存率为0.00；
11.04没有新用户，不输出。

2.思路分析

这题其实挺简单的，不知道为什么我想了好久才写出来。。。

题目要求统计2021年11月每天新用户的次日留存率。
1）首先要找到每个用户第一次的进入时间，并且过滤出来时间为2021-11的数据。
2）然后对这个结果按第一次进入时间（格式为2021-11-01）分组，直接count(*)即可求出每天的新增用户总数。
3）用1中得到的数据跟原表进行inner join，这样可以得到一张表，表中每行代表一个用户的一次进入数据外加上这个用户的第一次登录时间。我们可以发现，只要用户的第一次进入时间的后一天大于等于这个用户一次记录里的进入时间且小于等于离开时间，我们就知道这个用户第二天活跃了（当然用别的判断思路也可以，这个应该最简单）。但是这样可能会有重复数据，比如用户第二天进入和离开了两次，所以需要去重，然后count()即可。

PS：我们也可以把2中的求每天的总的新增用户数放到3中，直接count(distinct())也可以。

3.语句实现

select
    first_time,
    cast(count(distinct(if(date_add(first_time,INTERVAL 1 DAY)>=date(in_time) and date_add(first_time,INTERVAL 1 DAY)<=date(out_time),t1.uid,null)))*1.0/count(distinct(t1.uid)) as decimal(16,2)) uv_left_rate
from
(
    select
        uid,
        in_time,
        out_time
    from tb_user_log
)t1
inner join
(
    select
        uid,
        min(date(in_time)) first_time
    from tb_user_log
    group by uid
    having date_format(first_time,'%Y-%m')='2021-11'
)t2
on t1.uid=t2.uid
group by first_time
order by first_time

三、SQL3（较难)

1.题目内容

描述
用户行为日志表tb_user_log
在这里插入图片描述
问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

注：
用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
假设今天就是数据中所有日期的最大值。
近7天表示包含当天T的近7天，即闭区间[T-6, T]。

输出示例：
示例数据的输出结果如下
在这里插入图片描述
解释：
今天日期为2021.11.04，根据用户分级标准，用户行为日志表tb_user_log中忠实用户有：109、108、104；新晋用户有105、102；沉睡用户有103；流失用户有101；共7个用户，因此他们的比例分别为0.43、0.29、0.14、0.14。

2.思路分析

2.1 思路一

题目要求的是四个比率，那肯定要求出五个具体的数，四个比率对应部分的人数以及总人数。
忠实用户的判断条件：初次进入时间在今天的7天前&&最晚离开时间在今天的7天内
新晋用户的判断条件：初次进入时间在今天的7天内
沉睡用户的判断条件：最晚的离开（进入时间）位于7天外30天内
流失用户的判断条件：最晚的离开时间在今天的30天外
综上，我们需要拿到几个时间：今天（所有日期的最大值）、用户的初次进入时间、用户的最晚离开时间，有这三个时间就能分别求出对应的比率了。
要注意的是，最终的结果是四行不是一行，我想到的方法是把求出来最终比率的表用with as定义一下，然后分别求出对应的比率再union起来，就可以得到4行数据。（case when then不用union也可以写出来）

2.2 思路二

思路二是不用union，一次输出四行结果。
前面计算的思路跟思路一一样，在得到三种时间后，我们可以计算最晚登录时间跟今天的差值，大于等于30天为流失用户，大于等于7天为沉睡用户；初次进入时间与今天的差值小于7天为新晋用户；其余的为忠实用户。这样给每个用户打上标签，然后按照标签分组求和即可。

3.语句实现

3.1思路一语句

with tmp as
(
    select
        max(date(out_time)) today
    from tb_user_log
),
tmp1 as
(
    select
        count(distinct(if(first_time<date_add(today,INTERVAL '-6' DAY) and last_time>=date_add(today,INTERVAL '-6' DAY),uid,null))) num1,
        count(distinct(if(first_time>=date_add(today,INTERVAL '-6' DAY),uid,null))) num2,
        count(distinct(if(date_add(last_time,INTERVAL '6' DAY)<today and date_add(last_time,INTERVAL '29' DAY)>=today,uid,null))) num3,
        count(distinct(if(date_add(last_time,INTERVAL '29' DAY)<today,uid,null))) num4
    from
    (
        select
            t1.uid,
            in_time,
            out_time,
            first_time,
            last_time,
            today
        from
        (
            select
                uid,
                min(date(in_time)) first_time,
                max(date(in_time)) last_time
            from tb_user_log
            group by uid
        )t1
        inner join
        tb_user_log on
        t1.uid=tb_user_log.uid
        inner join
        tmp
    )t2
)


select
    '忠实用户',
    cast(num1*1.0/(num1+num2+num3+num4) as decimal(16,2))
from tmp1
union
select
    '新晋用户',
    cast(num2*1.0/(num1+num2+num3+num4) as decimal(16,2))
from tmp1
union
select
    '沉睡用户',
    cast(num3*1.0/(num1+num2+num3+num4) as decimal(16,2))
from tmp1
union
select
    '流失用户',
    cast(num4*1.0/(num1+num2+num3+num4) as decimal(16,2))
from tmp1

3.2 思路二语句

select
    user_grade,
    cast(count(*)*1.0/total_count as decimal(16,2)) ratio
from
(
    select
         CASE
             WHEN datediff(today,last_time)>=30 THEN '流失用户'
             WHEN datediff(today,last_time)>=7 THEN '沉睡用户'
             WHEN datediff(today,first_time)<7 THEN '新晋用户'
             ELSE '忠实用户'
         END as user_grade,
         uid,
         count(*) over() total_count
    from
    (
        select
            uid,
            min(date(in_time)) first_time,
            max(date(out_time)) last_time
        from tb_user_log
        group by uid
    )t1
    inner join
    (
        select
            max(date(out_time)) today
        from tb_user_log
    )t2
    on 1=1
)t3
group by user_grade
order by cast(count(*)*1.0/total_count as decimal(16,2)) desc

四、SQL4（较难)

1.题目内容

描述
用户行为日志表tb_user_log
在这里插入图片描述
问题：统计每天的日活数及新用户占比

注：
新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。
新用户占比保留2位小数，结果按日期升序排序。

输出示例：
示例数据的输出结果如下
在这里插入图片描述
解释：
2021年10月31日有2个用户活跃，都为新用户，新用户占比1.00；
2021年11月1日有3个用户活跃，其中1个新用户，新用户占比0.33；

2.思路分析

题目要求统计每天的日活数以及新用户占比，那么就可以转化为求每天的日活数、每天的新用户数，然后就可以计算出结果了。

每天的日活数：按用户的进入时间和离开时间进行统计（如果只按进入时间统计的话，发生了跨天的情况，会出现少统计的错误），可以将进入时间和离开时间拆开作为一个新的字段，浏览时间，按浏览时间进行分组求日活数。
每天的新用户数：要先知道每个用户的首次进入时间，然后在求日活时判断该天日期是否等于该用户的首日进入日期，然后进行统计即可。

3.语句实现

with tmp as
(
    select
        t1.uid,
        in_time,
        out_time,
        first_time
    from
    (
        select
            uid,
            date(in_time) in_time,
            date(out_time) out_time
        from tb_user_log
    )t1
    inner join
    (
        select
            uid,
            min(date(in_time)) first_time
        from tb_user_log
        group by uid
    )t2
    on t1.uid=t2.uid
)

select
    in_time,
    count(distinct(uid)),
    round(count(distinct(if(in_time=first_time,uid,null)))/count(distinct(uid)),2)
from
(
    select
        uid,
        in_time,
        first_time
    from tmp
    union
    select
        uid,
        out_time,
        first_time
    from tmp
)t1
group by in_time
order by in_time