跳到主要内容

快手大数据面试SQL-新人发布视频的首日曝光量

一、题目​

已知有三张表：

  • t1_user_register：用户注册表（user_id, register_time）
  • t1_video_publish：视频发布表（video_id, author_id, publish_time）
  • t1_video_exposure：视频曝光日志表（video_id, user_id, exposure_time，记录视频被推荐给用户的时间）

请统计每个新人作者（注册7天内首次发布视频的用户即为新人作者）发布的第一个视频在发布后24小时内的曝光次数（曝光次数 = 被推荐给多少不同的用户）。

输出字段：author_id, first_video_id, publish_time, exposure_cnt_24h。按曝光次数降序排列。

样例数据

t1_user_register：

+---------+----------------------+
| user_id |    register_time     |
+---------+----------------------+
| a001    | 2024-05-28 10:00:00  |
| a002    | 2024-06-01 00:00:00  |
| a003    | 2024-05-15 08:00:00  |
+---------+----------------------+

t1_video_publish：

+----------+-----------+----------------------+
| video_id | author_id |     publish_time     |
+----------+-----------+----------------------+
| V001     | a001      | 2024-05-30 08:00:00  |
| V002     | a001      | 2024-06-01 12:00:00  |
| V003     | a002      | 2024-06-01 10:00:00  |
| V004     | a002      | 2024-06-03 12:00:00  |
| V005     | a003      | 2024-06-01 08:00:00  |
+----------+-----------+----------------------+

t1_video_exposure：

+----------+---------+----------------------+
| video_id | user_id |    exposure_time     |
+----------+---------+----------------------+
| V001     | u001    | 2024-05-30 09:00:00  |
| V001     | u002    | 2024-05-30 10:00:00  |
| V001     | u003    | 2024-06-01 07:00:00  |
| V003     | u001    | 2024-06-01 11:00:00  |
| V003     | u002    | 2024-06-01 12:00:00  |
| V003     | u004    | 2024-06-01 13:00:00  |
| V003     | u005    | 2024-06-02 09:00:00  |
| V005     | u001    | 2024-06-01 10:00:00  |
| V005     | u003    | 2024-06-02 10:00:00  |
+----------+---------+----------------------+

三、思路分析​

本题是多表关联 + 多条件过滤 + 时间窗口计算的综合题目，逐步拆解即可：

  • 识别新人作者：用户注册后7天内（即 register_time + 7 days）首次发布视频的作者。需要用MIN找到首条视频发布时间，判断首条发布时间 <= register_time + 7天。

  • 找每个新人的第一个视频：对符合新人条件的作者，按author_id分组，取publish_time最小的那条视频，使用ROW_NUMBER窗口函数。

  • 计算首日曝光量：将第一个视频与曝光表关联，过滤曝光时间在 [publish_time, publish_time + 24h) 的记录，按video_id GROUP BY，COUNT DISTINCT user_id。

  • 注意：a003注册时间是5月15日，但第一条视频发布是6月1日，超过7天，不属于新人作者，应排除。

在快手的用户增长分析中，新人（新创作者）的首条视频曝光量是衡量冷启动质量和分配公平性的核心指标。

维度评分
题目难度⭐️⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️

四、逐步推导​

1.找出新人作者及其首条视频​

首先关联注册表和发布表，找到注册后7天内首次发布的作者，然后用ROW_NUMBER对每个作者按发布时间排序，取第一条（rn=1）。

执行SQL

with first_publish as (
    select
        v.author_id,
        v.video_id,
        v.publish_time,
        row_number() over (partition by v.author_id order by v.publish_time asc) as rn
    from t1_video_publish v
    inner join t1_user_register r
        on v.author_id = r.user_id
        and v.publish_time <= date_add(r.register_time, interval 7 day)
)
select
    author_id,
    video_id as first_video_id,
    publish_time
from first_publish
where rn = 1

执行结果

+-----------+----------------+----------------------+
| author_id | first_video_id |     publish_time     |
+-----------+----------------+----------------------+
| a001      | V001           | 2024-05-30 08:00:00  |
| a002      | V003           | 2024-06-01 10:00:00  |
+-----------+----------------+----------------------+

a003被排除（注册5月15日，首条视频6月1日，超过7天）。a001注册5月28日，首条视频5月30日，在7天内。a002注册6月1日，首条视频也在6月1日，属于新人。

2.计算首条视频发布后24小时内的曝光次数​

将新人首条视频与曝光表关联，过滤曝光时间在24小时内，统计去重曝光用户数。

执行SQL

with first_publish as (
    select
        v.author_id,
        v.video_id,
        v.publish_time,
        row_number() over (partition by v.author_id order by v.publish_time asc) as rn
    from t1_video_publish v
    inner join t1_user_register r
        on v.author_id = r.user_id
        and v.publish_time <= date_add(r.register_time, interval 7 day)
),
newcomer_video as (
    select
        author_id,
        video_id as first_video_id,
        publish_time
    from first_publish
    where rn = 1
)
select
    n.author_id,
    n.first_video_id,
    n.publish_time,
    count(distinct e.user_id) as exposure_cnt_24h
from newcomer_video n
left join t1_video_exposure e
    on n.first_video_id = e.video_id
    and e.exposure_time >= n.publish_time
    and e.exposure_time < date_add(n.publish_time, interval 1 day)
group by n.author_id, n.first_video_id, n.publish_time
order by exposure_cnt_24h desc

执行结果

+-----------+----------------+----------------------+-------------------+
| author_id | first_video_id |     publish_time     | exposure_cnt_24h  |
+-----------+----------------+----------------------+-------------------+
| a001      | V001           | 2024-05-30 08:00:00  | 2                 |
| a002      | V003           | 2024-06-01 10:00:00  | 3                 |
+-----------+----------------+----------------------+-------------------+

分析：新人a002的首条视频V003在24小时内获得了3次曝光（推荐给了3个不同用户），表现较好；a001的首条视频V001获得了2次曝光。

3.(可选) 过滤曝光记录以验证计算结果​

可以查看每条新人视频实际匹配到的曝光记录明细，方便验证。

执行SQL

with newcomer_video as (
    select 'V001' as first_video_id, '2024-05-30 08:00:00' as publish_time
    union all
    select 'V003', '2024-06-01 10:00:00'
)
select
    n.first_video_id,
    n.publish_time,
    e.user_id,
    e.exposure_time,
    case
        when e.exposure_time >= n.publish_time
         and e.exposure_time < date_add(n.publish_time, interval 1 day)
        then '24h内'
        else '超出24h'
    end as in_window
from newcomer_video n
left join t1_video_exposure e
    on n.first_video_id = e.video_id
order by n.first_video_id, e.exposure_time

执行结果

+----------------+----------------------+---------+----------------------+-----------+
| first_video_id |     publish_time     | user_id |    exposure_time     | in_window |
+----------------+----------------------+---------+----------------------+-----------+
| V001           | 2024-05-30 08:00:00  | u001    | 2024-05-30 09:00:00  | 24h内     |
| V001           | 2024-05-30 08:00:00  | u002    | 2024-05-30 10:00:00  | 24h内     |
| V001           | 2024-05-30 08:00:00  | u003    | 2024-06-01 07:00:00  | 超出24h   |
| V003           | 2024-06-01 10:00:00  | u001    | 2024-06-01 11:00:00  | 24h内     |
| V003           | 2024-06-01 10:00:00  | u002    | 2024-06-01 12:00:00  | 24h内     |
| V003           | 2024-06-01 10:00:00  | u004    | 2024-06-01 13:00:00  | 24h内     |
| V003           | 2024-06-01 10:00:00  | u005    | 2024-06-02 09:00:00  | 超出24h   |
+----------------+----------------------+---------+----------------------+-----------+

五、常见坑点​

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三​

  1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

  2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

  3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结​

考点说明
ROW_NUMBER + 差值法连续问题经典解法：日期-行号=分组标识，相同差值同一连续段
多表JOINLEFT JOIN保留主表所有数据，COALESCE处理NULL
COUNT DISTINCT 去重统计唯一用户/事件数，避免重复记录干扰聚合结果
GROUP BY + 聚合函数分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入​

点击展开 DDL & DML
--建表语句
CREATE TABLE IF NOT EXISTS t1_user_register (
    user_id STRING COMMENT '用户ID',
    register_time STRING COMMENT '注册时间'
);

CREATE TABLE IF NOT EXISTS t1_video_publish (
    video_id STRING COMMENT '视频ID',
    author_id STRING COMMENT '作者ID',
    publish_time STRING COMMENT '发布时间'
);

CREATE TABLE IF NOT EXISTS t1_video_exposure (
    video_id STRING COMMENT '视频ID',
    user_id STRING COMMENT '被曝光的用户ID',
    exposure_time STRING COMMENT '曝光时间'
);

--数据插入
INSERT INTO t1_user_register(user_id, register_time) VALUES
('a001', '2024-05-28 10:00:00'),
('a002', '2024-06-01 00:00:00'),
('a003', '2024-05-15 08:00:00');

INSERT INTO t1_video_publish(video_id, author_id, publish_time) VALUES
('V001', 'a001', '2024-05-30 08:00:00'),
('V002', 'a001', '2024-06-01 12:00:00'),
('V003', 'a002', '2024-06-01 10:00:00'),
('V004', 'a002', '2024-06-03 12:00:00'),
('V005', 'a003', '2024-06-01 08:00:00');

INSERT INTO t1_video_exposure(video_id, user_id, exposure_time) VALUES
('V001', 'u001', '2024-05-30 09:00:00'),
('V001', 'u002', '2024-05-30 10:00:00'),
('V001', 'u003', '2024-06-01 07:00:00'),
('V003', 'u001', '2024-06-01 11:00:00'),
('V003', 'u002', '2024-06-01 12:00:00'),
('V003', 'u004', '2024-06-01 13:00:00'),
('V003', 'u005', '2024-06-02 09:00:00'),
('V005', 'u001', '2024-06-01 10:00:00'),
('V005', 'u003', '2024-06-02 10:00:00');
📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

微信公众号二维码
💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题

交流微信二维码

你可能还想看

相关题目
02. 用户中两人一定认识的组合数04. 直播间礼物价值Top N观众05. 关注关系：互相关注的用户对