【SQL常见场景题】一用户增长场景（某度信息流）

爱写代码的July

已于 2024-08-28 22:29:06 修改

阅读量583

点赞数 22

分类专栏： SQL习题带刷文章标签： sql 数据库算法面试 mysql

于 2024-08-28 19:58:17 首次发布

本文链接：https://blog.csdn.net/Liu_y_xin/article/details/141368457

版权

SQL习题带刷专栏收录该内容

28 篇文章 0 订阅

订阅专栏

题目一：2021年11月每天的人均浏览文章时长

题目要求：

场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

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

表结构：

运行结果示例：

思路：

计算浏览时长可以使用timestampdiff（）函数实现，其中包括三个参数：计算单位、起始时间、结束时间。使用timestampdiff（）函数我们可以很容易的计算出两个时间数值的差值。

同时我们需要按照日期进行分组统计，因此要将进出入时间字段的日期部分提取出来，我们可以直接使用date（）函数实现，也可以使用字符串截取函数进行截取。

人均浏览文章时长的计算也就是用我们之前算过的浏览时长之和除以总人数得出，这里用到的聚合函数是sum（）函数和count（）函数。需要注意的是我们统计总人数时要进行一个去重的操作。得出人均浏览文章时长的结果后，我们还需要使用round（）函数处理一下结果，使其四舍五入保留1位小数。并最终按照该字段排序。

此外，我们需要限定时间条件为2021年11月期间，这里我们同样可以用字符串截取函数实现，截取时间字段的前7位。并且需要限定条件artical_id不为0（题目说明artical_id-文章ID为0表示用户在非文章内容页）。

运行代码示例：

select date(in_time) as dt,round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid) ,1) as avg_viiew_len_sec
from tb_user_log
where left(in_time,7) = '2021-11' and artical_id !=0
group by dt
order by avg_viiew_len_sec

题目二：每篇文章同一时刻最大在看人数

题目要求：

场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。

表结构：

运行结果示例：

思路：

涉及到同一时刻的数据统计，我们可以使用窗口函数来进行。

首先我们将log表中的进、出时间合并，并设置一个flag值标注当前用户在该时刻是进入还是离开。这里我们用1表示用户进入，-1表示用户离开。示例结果如下，如id9001的文章在2021-11-01 10:00:00和10:00:09、10:00:28、10:00:10分别有一个用户进入，在10:00:11时一个用户退出。

在此表的基础上，我们使用窗口函数按照时间顺序来统计每个文章的flag之和，也就是计算在不同时刻的在看人数。需要注意的是，由于同一时刻有进入也有离开时，先记录用户数增加再记录减少，因此在排序字段中我们还需要加上flag字段，当时间相同时，我们按照flag值来进行加和汇总，最终得到的数据如下：

如此，我们就得到了每篇文章在不同时刻的浏览人数，接下来只要从该表中获取到每篇文章的最大在看人数即可。

运行代码示例：

select artical_id,max(cnt) as max_uv
from (
    select artical_id,totoltime,
    sum(flag) over (PARTITION BY artical_id ORDER BY totoltime,flag DESC) as cnt
    from (
        select artical_id,in_time as totoltime,1 as flag
        from tb_user_log
        union all
        select artical_id,out_time as totoltime,-1 as flag
        from tb_user_log
    )t
)t1
where artical_id != 0
group by artical_id
ORDER BY max_uv DESC

题目三：2021年11月每天新用户的次日留存率

题目要求：

问题：统计2021年11月每天新用户的次日留存率（保留2位小数）

注：

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

表结构：

运行结果示例：

思路：

我们首先需要找到所有用户的注册时间，也就是每个用户最早一次进入的时间，之后我们将其与log表连接，找到第二天又活跃了的用户（也就是in_time 和login_time 的时间差为1的情况）。得到结果如下：

从该表中我们可以看出，101用户在11-01注册，在11-02又活跃了。103用户同样如此。但是102用户在11-01注册，但是第二天并没有登录。因此11月1日的次日留存率为2/3。同理，11月2日的次日留存率为1,11月3日的次日留存率为0。

我们接下来使用SQL语句实现计算次日留存率，不难得出，次日留存率等于 count（in_time）/ count(login_time)，并按照日期进行分组，使用round（）函数保留两位小数。

运行代码示例：


select date(login_time) as dt, round(count(in_time)/ count(login_time) ,2) as uv_left_rate
from (
select t.uid,in_time,t.login_time
from (
    select uid,min(in_time) as login_time
    from tb_user_log
    group by uid
) t
left join tb_user_log log
on t.uid = log.uid and datediff(log.in_time,t.login_time) =1
) t2
where left(login_time,7) = '2021-11'
group by date(login_time)
order by dt

题目四：统计活跃间隔对用户分级结果

题目要求：

问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

注：

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

表结构：

运行结果示例：

思路：

这道题目比较复杂，我们需要借助多个中间表完成。首先，我们需要统计每个用户最早最晚的活跃日期，这里我们直接使用min（）和max（）函数就可以完成。之后我们获取当前日期和总的用户数，并合并两张表。最后我们计算每个用户最早/最晚活跃日期距离当前日期的时间，同样使用timestampdiff（）函数实现。到这里我们就已经完成了题目的主体部分了。

接下来，我们使用case when子句进行判断，为每个用户打上分级标签，之后再按照标签分组统计即可。

运行代码示例：

SELECT user_grade, ROUND(COUNT(uid) / MAX(user_cnt), 2) as ratio
FROM (
    SELECT uid, user_cnt,
        CASE
            WHEN last_dt_diff >= 30 THEN "流失用户"
            WHEN last_dt_diff >= 7 THEN "沉睡用户"
            WHEN first_dt_diff < 7 THEN "新晋用户"
            ELSE "忠实用户"
        END as user_grade
    FROM (
        SELECT uid, user_cnt,
            TIMESTAMPDIFF(DAY,first_dt,cur_dt) as first_dt_diff, 
            TIMESTAMPDIFF(DAY,last_dt,cur_dt) as last_dt_diff
        FROM (
            SELECT uid, MIN(DATE(in_time)) as first_dt,
                MAX(DATE(out_time)) as last_dt
            FROM tb_user_log
            GROUP BY uid
        ) as t_uid_first_last
        LEFT JOIN (
            SELECT MAX(DATE(out_time)) as cur_dt,
                COUNT(DISTINCT uid) as user_cnt
            FROM tb_user_log
        ) as t_overall_info ON 1
    ) as t_user_info
) as t_user_grade
GROUP BY user_grade
ORDER BY ratio DESC;

题目五：每天的日活数及新用户占比

题目要求：

问题：统计每天的日活数及新用户占比

注：

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

表结构：

运行结果示例：

思路：

本题和上面几道题目是一样的，使用窗口函数就可以完成。本题我们尝试不使用窗口函数，换一个思路解决。

首先我们还是需要统计出所有用户的注册日期，我们同样使用min（）函数来完成，通过min（）函数得到注册日期。之后，我们将这张注册日期表与log表连接，得到用户的注册日期和进入日期的关联表，但由于存在用户in_time-进入时间和out_time-离开时间跨天的现象，所以我们可以使用union连接关联注册日期和进出日期的综合表，例如：

在该表的基础上，我们就可以进行计算新用户占比了，首先判断两个日期是否相同，相同则说明是新用户，不同则说明不是新用户，我们使用case when语句结合count（）函数统计新用户的数量，再使用count（）直接统计当天活跃用户的人数即可，需要注意的是存在某一个用户当天多次活跃的情况，因此我们需要使用distinct关键字去重。

运行代码示例：

select 
    date(new_time)as dt, 
    count(distinct uid) as dau,
    round(sum(case when login_time = new_time then 1 else 0 end) / count(distinct uid) ,2) as uv_new_ratio
from (
    select t.uid,login_time,in_time as new_time
    from (
        select uid, min(in_time) as login_time
        from tb_user_log 
        group by uid
    ) t
    left join tb_user_log l
    using(uid)
    union
    select t.uid,login_time,out_time as new_time
    from (
        select uid, min(in_time) as login_time
        from tb_user_log 
        group by uid
    ) t
    left join tb_user_log l
    using(uid)
) t2
group by date(new_time)
order by date(new_time)

题目六：连续签到领金币

题目要求：

场景逻辑说明：

artical_id-文章ID代表用户浏览的文章的ID，特殊情况artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。注意：只有artical_id为0时sign_in值才有效。
从2021年7月7日0点开始，用户每天签到可以领1金币，并可以开始累积签到天数，连续签到的第3、7天分别可额外领2、6金币。
每连续签到7天后重新累积签到天数（即重置签到天数：连续第8天签到时记为新的一轮签到的第一天，领1金币）

问题：计算每个用户2021年7月以来每月获得的金币数（该活动到10月底结束，11月1日开始的签到不再获得金币）。结果按月份、ID升序排序。

注：如果签到记录的in_time-进入时间和out_time-离开时间跨天了，也只记作in_time对应的日期签到了。

表结构：

运行结果示例：

思路：

签到问题也是SQL中较为经典的题型之一，在实际业务中也经常会遇到。我们可以首先将用户的签到记录按日期排序，并计算每个签到记录相对于第一个签到日的天数偏移，通过这个天数偏移的差值，我们可以判断是否存在中断，进而重置连续签到的天数。

运行代码示例：

WITH UserSignIn AS (
    -- 选择所有有效的签到记录
    SELECT 
        uid,
        DATE(in_time) AS sign_date,
        EXTRACT(YEAR_MONTH FROM in_time) AS month
    FROM 
        tb_user_log
    WHERE 
        artical_id = 0 AND sign_in = 1 
        AND in_time < '2021-11-01'
),
SignInWithRank AS (
    -- 为每个用户的签到记录按日期进行排序，并计算日期偏移
    SELECT 
        uid,
        sign_date,
        month,
        DATEDIFF(sign_date, MIN(sign_date) OVER (PARTITION BY uid ORDER BY sign_date)) AS day_offset
    FROM 
        UserSignIn
),
SignInStreaks AS (
    -- 通过 day_offset 计算连续签到天数
    SELECT 
        uid,
        sign_date,
        month,
        ROW_NUMBER() OVER (PARTITION BY uid ORDER BY sign_date) 
        - day_offset AS streak_group
    FROM 
        SignInWithRank
),
SignInCoins AS (
    -- 计算每个用户的连续签到天数和每日金币
    SELECT 
        uid,
        month,
        sign_date,
        ROW_NUMBER() OVER (PARTITION BY uid, streak_group ORDER BY sign_date) AS streak_day,
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY uid, streak_group ORDER BY sign_date) = 3 THEN 3 -- 连续3天，1+2=3金币
            WHEN ROW_NUMBER() OVER (PARTITION BY uid, streak_group ORDER BY sign_date) = 7 THEN 7 -- 连续7天，1+6=7金币
            ELSE 1 -- 其他情况每次签到1金币
        END AS daily_coin
    FROM 
        SignInStreaks
),
UserMonthlyCoins AS (
    -- 计算每月的总金币
    SELECT 
        uid,
        month,
        SUM(daily_coin) AS coin
    FROM 
        SignInCoins
    GROUP BY 
        uid, month
)
-- 按用户ID和月份排序输出
SELECT 
    uid,
    month,
    coin
FROM 
    UserMonthlyCoins
ORDER BY 
    month, uid;