跳到主要内容

SQL 新人首日曝光量：新注册用户首日视频曝光统计（快手面试题）

一、题目​

已知有三张表：

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

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

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

样例数据

t8_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  |
+----------+----------------------+

t8_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  |
+-----------+------------+----------------------+

t8_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 t8_video_publish v
    inner join t8_user_register r
        on v.author_id = r.user_id
        and unix_timestamp(v.publish_time) <= unix_timestamp(r.register_time) + 7 * 86400
)
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  |
+------------+-----------------+----------------------+
2 rows selected (1.456 seconds)(https://www.dwsql.com)

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 t8_video_publish v
    inner join t8_user_register r
        on v.author_id = r.user_id
        and unix_timestamp(v.publish_time) <= unix_timestamp(r.register_time) + 7 * 86400
),
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 t8_video_exposure e
    on n.first_video_id = e.video_id
    and unix_timestamp(e.exposure_time) >= unix_timestamp(n.publish_time)
    and unix_timestamp(e.exposure_time) < unix_timestamp(n.publish_time) + 86400
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  |
+------------+-----------------+----------------------+-------------------+
| a002       | V003            | 2024-06-01 10:00:00  | 4                 |
| a001       | V001            | 2024-05-30 08:00:00  | 2                 |
+------------+-----------------+----------------------+-------------------+
2 rows selected (1.477 seconds)(https://www.dwsql.com)

分析：新人a002的首条视频V003在24小时内获得了4次曝光（推荐给了4个不同用户），表现较好；a001的首条视频V001获得了2次曝光。注意 V003 的 u005 曝光时间 06-02 09:00，距发布 06-01 10:00 只有 23 小时，仍在 24 小时窗口内——如果用 date_add（截断到次日零点）会把它错误排除。

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 unix_timestamp(e.exposure_time) >= unix_timestamp(n.publish_time)
         and unix_timestamp(e.exposure_time) < unix_timestamp(n.publish_time) + 86400
        then '24h内'
        else '超出24h'
    end as in_window
from newcomer_video n
left join t8_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内       |
+-----------------+----------------------+----------+----------------------+------------+
7 rows selected (0.563 seconds)(https://www.dwsql.com)

四、常见坑点​

坑1：date_add 截断时间，不能用于「严格24小时」或「7天」的时分秒级判断 — Spark 的 date_add(dt, n) 返回日期类型、会丢弃时分秒。例如 date_add('2024-06-01 10:00:00', 1) 结果是 2024-06-02 00:00:00，把「24小时」错算成「次日零点」，导致 06-02 00:00~10:00 之间的曝光被漏掉（本例 V003 的 u005 曝光 06-02 09:00 就因此被误排除）。要精确到秒，必须用 unix_timestamp 转秒数再加 86400 比较。注册7天同理，用 unix_timestamp + 7 * 86400。

坑2：新人判断条件 — 新人的定义是"注册7天内发布首个视频"，判定的是首条视频的 publish_time &lt;= register_time + 7 天，而不是要求所有视频都在7天内。需先确定首条视频再判断，避免用后续视频做判定。

坑3：ROW_NUMBER 取首条视频 — 每个作者可能发布多个视频，须用 ROW_NUMBER 按 publish_time 升序取 rn=1 作为首条视频；直接按 author_id 分组取视频会丢失"首个"语义，导致曝光量口径错误。

五、知识点总结​

考点说明
unix_timestamp 秒数计算时间转秒数后 + 86400（24小时）/ + 7*86400（7天），精确到秒，避免 date_add 截断时分秒
ROW_NUMBER 取首条partition by author_id order by publish_time，rn=1 定位每个新人的第一个视频
多表 JOIN + 条件过滤注册表与发布表按用户关联，JOIN ON 中过滤 publish_time <= 注册时间+7天
COUNT(DISTINCT user_id)曝光次数按去重用户统计，同一用户多次曝光只算一次

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

点击展开 DDL & DML
--建表语句
create table if not exists t8_user_register (
    user_id string comment '用户ID',
    register_time string comment '注册时间'
);

create table if not exists t8_video_publish (
    video_id string comment '视频ID',
    author_id string comment '作者ID',
    publish_time string comment '发布时间'
);

create table if not exists t8_video_exposure (
    video_id string comment '视频ID',
    user_id string comment '被曝光的用户ID',
    exposure_time string comment '曝光时间'
);

--数据插入
insert into t8_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 t8_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 t8_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真题

交流微信二维码

你可能还想看

相关题目
05. 互相关注的用户对07. 热门话题Top10及参与用户数01. 直播间最高同时在线人数