• 【牛客刷题-SQL大厂面试真题】NO2.用户增长场景（某度信息流）

    在这里插入图片描述
    体系化学习SQL，请到牛客经典高频面试题库，参加实训，提高你的SQL技能吧~

    https://www.nowcoder.com/link/pc_csdncpt_itbd_sql

    文章目录

    前言

    SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

    在这里插入图片描述

    🐴 SQL162 2021年11月每天的人均浏览文章时长

    🚀 建表语句

    DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:31', 0),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:24', 0),
  (102, 9002, '2021-11-01 11:00:00', '2021-11-01 11:00:11', 0),
  (101, 9001, '2021-11-02 10:00:00', '2021-11-02 10:00:50', 0),
  (102, 9002, '2021-11-02 11:00:01', '2021-11-02 11:00:24', 0);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    📖 需求

    场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，
artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。
问题：统计2021年11月每天的人均浏览文章时长（秒数），
结果保留1位小数，并按时长由短到长排序。

    • 1
    • 2
    • 3
    • 4

    🍌🍌 答案

    select date_format(in_time,"%Y-%m-%d")dt,
round (sum(timestampdiff(second,in_time,out_time))
/count(distinct uid),1) avg_view_len_sec
from tb_user_log
where month(in_time)= 11 and artical_id !=0
group by dt
order by avg_view_len_sec

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    🐴 SQL163 每篇文章同一时刻最大在看人数

    🚀 建表语句

    DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:11', 0),
  (102, 9001, '2021-11-01 10:00:09', '2021-11-01 10:00:38', 0),
  (103, 9001, '2021-11-01 10:00:28', '2021-11-01 10:00:58', 0),
  (104, 9002, '2021-11-01 11:00:45', '2021-11-01 11:01:11', 0),
  (105, 9001, '2021-11-01 10:00:51', '2021-11-01 10:00:59', 0),
  (106, 9002, '2021-11-01 11:00:55', '2021-11-01 11:01:24', 0),
  (107, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    📖 需求

    场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，
artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。
问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，
先记录用户数增加再记录减少，结果按最大人数降序。

    • 1
    • 2
    • 3
    • 4

    🍌🍌 答案

    SELECT artical_id, max(t1) as t2
FROM(
    SELECT artical_id,t,
    sum(m)over(partition by artical_id order by t,m desc) as t1
    from(
        select artical_id,in_time as t,1 as m
        from tb_user_log
        where artical_id<>0
        UNION ALL
        select artical_id,out_time as t,-1 as m
        from tb_user_log
        where artical_id<>0
    )a
)b
group by artical_id
order by t2 desc

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    在这里插入图片描述

    🐴 SQL164 2021年11月每天新用户的次日留存率

    🚀 建表语句

    DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 0, '2021-11-01 10:00:00', '2021-11-01 10:00:42', 1),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:09', 0),
  (103, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0),
  (101, 9002, '2021-11-02 10:00:09', '2021-11-02 10:00:28', 0),
  (103, 9002, '2021-11-02 10:00:51', '2021-11-02 10:00:59', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (101, 9003, '2021-11-03 11:00:55', '2021-11-03 11:01:24', 0),
  (104, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (101, 9002, '2021-11-04 11:00:55', '2021-11-04 11:00:59', 0);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    📖 需求

    问题：统计2021年11月每天新用户的次日留存率（保留2位小数）
注：
次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
如果in_time-进入时间和out_time-离开时间跨天了，
在两天里都记为该用户活跃过，结果按日期升序。

    • 1
    • 2
    • 3
    • 4
    • 5

    🍌🍌 答案

    select a.first_day, round(count(distinct b.uid)/count(distinct a.uid),2) as rate
from (
    select uid, date(min(in_time)) as first_day
    from tb_user_log 
    -- where date_format(in_time, '%Y-%m') = '2021-11'
    group by uid
    having date_format(min(in_time), '%Y-%m') = '2021-11') a 
left join (
    select uid, date(in_time) as dt 
    from tb_user_log
    union 
    select uid, date(out_time) as dt
    from tb_user_log) b 
on a.uid = b.uid and datediff(b.dt, a.first_day) = 1
group by a.first_day
order by a.first_day

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    在这里插入图片描述

    🐴 SQL165 统计活跃间隔对用户分级结果

    🚀 建表语句

    DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (109, 9001, '2021-08-31 10:00:00', '2021-08-31 10:00:09', 0),
  (109, 9002, '2021-11-04 11:00:55', '2021-11-04 11:00:59', 0),
  (108, 9001, '2021-09-01 10:00:01', '2021-09-01 10:01:50', 0),
  (108, 9001, '2021-11-03 10:00:01', '2021-11-03 10:01:50', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (104, 9003, '2021-09-03 11:00:45', '2021-09-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (102, 9001, '2021-10-30 10:00:00', '2021-10-30 10:00:09', 0),
  (103, 9001, '2021-10-21 10:00:00', '2021-10-21 10:00:09', 0),
  (101, 0, '2021-10-01 10:00:00', '2021-10-01 10:00:42', 1);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    📖 需求

    问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。
注：
用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、
沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
假设今天就是数据中所有日期的最大值。
近7天表示包含当天T的近7天，即闭区间[T-6, T]。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    🍌🍌 答案

    select user_grade,round(count(uid)
/(select count(distinct uid) from tb_user_log),2) q
from  
(
select uid,(case when datediff((select max(in_time) from tb_user_log),max(in_time)) <=6 
 and  datediff((select max(in_time) from tb_user_log),min(in_time)) >6 
 then '忠实用户'
 when datediff((select max(in_time) from tb_user_log),max(in_time)) <=6 
 and  datediff((select max(in_time) from tb_user_log),min(in_time)) <=6 
 then '新晋用户'
 when datediff((select max(in_time) from tb_user_log),max(in_time)) >6 
 and  datediff((select max(in_time) from tb_user_log),min(in_time)) <=29 
 then '沉睡用户'
 else '流失用户' end ) user_grade
from tb_user_log
group by uid
) f1
group by user_grade
order by q desc

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    在这里插入图片描述

    🐴 SQL166 每天的日活数及新用户占比

    🚀 建表语句

    DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-10-31 10:00:00', '2021-10-31 10:00:09', 0),
  (102, 9001, '2021-10-31 10:00:00', '2021-10-31 10:00:09', 0),
  (101, 0, '2021-11-01 10:00:00', '2021-11-01 10:00:42', 1),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:09', 0),
  (108, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0),
  (108, 9001, '2021-11-02 10:00:01', '2021-11-02 10:01:50', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (106, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (108, 9001, '2021-11-03 10:00:01', '2021-11-03 10:01:50', 0),
  (109, 9002, '2021-11-03 11:00:55', '2021-11-03 11:00:59', 0),
  (104, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (106, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    📖 需求

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

    • 1
    • 2
    • 3
    • 4
    • 5

    🍌🍌 答案

    select dt, count(*) as dau, round(sum(new)/count(*), 2) as uv_new_ratio
from (
select uid, dt, case when dt = first_dt then 1 else 0 end as new
from 
(select uid, date(in_time) as dt
from tb_user_log
UNION
select uid, date(out_time) as dt
from tb_user_log) t1 
left join
(select uid, min(date(in_time)) as first_dt
from tb_user_log
group by uid) t2
using(uid)
) t
group by dt
order by dt

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    在这里插入图片描述

    🐴 SQL167 连续签到领金币

    🚀 建表语句

    DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 0, '2021-07-07 10:00:00', '2021-07-07 10:00:09', 1),
  (101, 0, '2021-07-08 10:00:00', '2021-07-08 10:00:09', 1),
  (101, 0, '2021-07-09 10:00:00', '2021-07-09 10:00:42', 1),
  (101, 0, '2021-07-10 10:00:00', '2021-07-10 10:00:09', 1),
  (101, 0, '2021-07-11 23:59:55', '2021-07-11 23:59:59', 1),
  (101, 0, '2021-07-12 10:00:28', '2021-07-12 10:00:50', 1),
  (101, 0, '2021-07-13 10:00:28', '2021-07-13 10:00:50', 1),
  (102, 0, '2021-10-01 10:00:28', '2021-10-01 10:00:50', 1),
  (102, 0, '2021-10-02 10:00:01', '2021-10-02 10:01:50', 1),
  (102, 0, '2021-10-03 11:00:55', '2021-10-03 11:00:59', 1),
  (102, 0, '2021-10-04 11:00:45', '2021-10-04 11:00:55', 0),
  (102, 0, '2021-10-05 11:00:53', '2021-10-05 11:00:59', 1),
  (102, 0, '2021-10-06 11:00:45', '2021-10-06 11:00:55', 1);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    📖 需求

    场景逻辑说明：
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对应的日期签到了。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    🍌🍌 答案

    SELECT uid,DATE_FORMAT(sign_dt,'%Y%m')as month,sum(coin)
FROM
(SELECT uid,sign_dt,TIMESTAMPADD(day,-diff+1,sign_dt)as start_day ,
case (DENSE_RANK() over (PARTITION by uid,TIMESTAMPADD(day,-diff+1,sign_dt) ORDER BY sign_dt))%7
WHEN 3 then 3 
WHEN 0 THEN 7
ELSE 1 end as coin
FROM
(SELECT uid ,DATE_FORMAT(in_time,'%Y%m%d')as sign_dt,
DENSE_RANK() over(PARTITION by uid ORDER BY in_time) as diff 
FROM tb_user_log
WHERE DATE_FORMAT(in_time,'%Y%m%d') BETWEEN 20210707 and 20211031 
AND artical_id =0 AND sign_in =1 )t1 )t2 
GROUP BY uid,DATE_FORMAT(sign_dt,'%Y%m')
ORDER BY DATE_FORMAT(sign_dt,'%Y%m') ,uid

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述
    在这里插入图片描述

  • 相关阅读:
    C语言系统化精讲（三）：C语言变量和数据类型-上篇
    matplotlib图表常用设置(四)
    OPENSSL-PKCS7入门知识介绍
    什么是MVC
    LabVIEW异步和同步通信详细分析及比较
    阿里云linux centos系统jdk版本始终换不了OpenJDK害人
    Android中级——MVVM
    MyCat2的介绍与安装以及基本使用
    LeetCode-61-旋转链表
    【AI必备利器】GPU白嫖指南
  • 原文地址：https://blog.csdn.net/weixin_41645135/article/details/125551330