8、刷牛客网SQL题(二)

---

题目来源

牛客网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)