字节跳动大数据面试SQL-用户复购率计算

数据仓库晨曦

发布于 2026-06-29 12:34:55

300

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

这道题来自字节跳动电商业务的数据分析岗面试。复购率是衡量用户忠诚度的核心指标——用户买了一次之后，还会再买吗？对于抖音电商这样的平台，复购率直接关系到 LTV（用户生命周期价值）。

业务场景：运营团队每月需要"复购率报表"。高复购率说明商品质量和用户体验好，低复购率可能意味着"一锤子买卖"——需要优化售后、推荐算法和会员体系。

二、题目

现有一张订单表 t16_zj_orders，记录了用户的每一笔订单。请计算每月的复购率。

t16_zj_orders 表

+-----------+----------+-------------+
| order_id  | user_id  | order_date  |
+-----------+----------+-------------+
| 1         | 1        | 2025-01-05  |
| 2         | 2        | 2025-01-10  |
| 3         | 1        | 2025-01-15  |
| 4         | 3        | 2025-01-20  |
| 5         | 1        | 2025-02-03  |
| 6         | 2        | 2025-02-10  |
| 7         | 4        | 2025-02-15  |
| 8         | 3        | 2025-02-20  |
| 9         | 1        | 2025-03-05  |
| 10        | 2        | 2025-03-10  |
+-----------+----------+-------------+

定义：

首单：用户第一次下单
复购单：首单之后再次下单即为复购单
总下单用户数：在当月下单的用户数
复购用户数：在当月下单，且在当月之前的任意时间也有过下单记录的用户数
复购率 = 当月复购用户数 / 当月总下单用户数

三、思路分析

本题的关键是判断"这个用户是不是第一次在平台下单"：

用 ROW_NUMBER() 按用户分区、按时间排序，标记每笔订单是该用户的第几单
rn = 1 的是首单，rn > 1 的是复购单
按月聚合：复购用户数 / 总下单用户数

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

四、逐步推导

步骤1：给每个订单标记序号，根据序号判断是首单还是复购单

Spark SQL

SELECT
    user_id,
    order_id,
    order_date,
    ROW_NUMBER() OVER (PARTITIONBY user_id ORDERBY order_date) AS rn,
    CASEWHEN ROW_NUMBER() OVER (PARTITIONBY user_id ORDERBY order_date) = 1THEN0
         ELSE1
    ENDAS is_repurchase --0: 首单，1: 复购单
FROM t16_zj_orders;

执行结果

+----------+-----------+-------------+-----+----------------+
| user_id  | order_id  | order_date  | rn  | is_repurchase  |
+----------+-----------+-------------+-----+----------------+
| 1        | 1         | 2025-01-05  | 1   | 0              |
| 1        | 3         | 2025-01-15  | 2   | 1              |
| 1        | 5         | 2025-02-03  | 3   | 1              |
| 1        | 9         | 2025-03-05  | 4   | 1              |
| 2        | 2         | 2025-01-10  | 1   | 0              |
| 2        | 6         | 2025-02-10  | 2   | 1              |
| 2        | 10        | 2025-03-10  | 3   | 1              |
| 3        | 4         | 2025-01-20  | 1   | 0              |
| 3        | 8         | 2025-02-20  | 2   | 1              |
| 4        | 7         | 2025-02-15  | 1   | 0              |
+----------+-----------+-------------+-----+----------------+
10 rows selected (0.352 seconds)(https://www.dwsql.com)

步骤2：按月聚合算复购率

SELECT
    month,
    COUNT(DISTINCT user_id) AS total_users,
    COUNT(DISTINCTCASEWHEN is_repurchase = 1THEN user_id END) AS repurchase_users,
    ROUND(COUNT(DISTINCTCASEWHEN is_repurchase = 1THEN user_id END)* 100.0 / COUNT(DISTINCT user_id), 2 ) AS repurchase_rate
FROM (
    SELECT
        DATE_FORMAT(order_date, 'yyyy-MM') ASmonth,
        user_id,
        CASEWHEN ROW_NUMBER() OVER (PARTITIONBY user_id ORDERBY order_date) > 1THEN1ELSE0ENDAS is_repurchase
    FROM t16_zj_orders
) t
GROUPBYmonth
ORDERBYmonth

最终结果：

+----------+--------------+-------------------+------------------+
|  month   | total_users  | repurchase_users  | repurchase_rate  |
+----------+--------------+-------------------+------------------+
| 2025-01  | 3            | 1                 | 33.33            |
| 2025-02  | 4            | 3                 | 75.00            |
| 2025-03  | 2            | 2                 | 100.00           |
+----------+--------------+-------------------+------------------+
3 rows selected (10.109 seconds)(https://www.dwsql.com)

五、常见坑点

坑1：一个月内多次购买的计数

用户在同一个自然月内可能有多笔订单（如用户1在1月有2单）。ROW_NUMBER 会正确标记第2单为复购。但如果用户在这个月的第一单恰好是他在平台的首次购买，那只有第2单及之后才算复购。

坑2：复购率 ≠ 留存率

复购率和留存率经常被混淆。留存率看的是"某天/月新增的用户，在后一天/月是否还活跃"（按人维度，只看是否回来）。复购率看的是"某月所有下单用户中，有多少是回头客"（按订单维度）。

六、举一反三

单品复购率：加上 product_id 维度，统计"用户对同一个商品的复购率"——这需要 PARTITION BY user_id, product_id
复购周期：统计用户两次购买之间的平均间隔天数，用 LAG(order_date) 计算

七、知识点总结

考点	说明
ROW_NUMBER	按用户+时间排序，标记第N单
rn > 1	判断是否为复购（非首次）
DATE_FORMAT	提取月份用于 GROUP BY
条件聚合	SUM(CASE WHEN) 统计复购用户数

八、建表语句和数据插入

CREATE TABLEIFNOTEXISTS t16_zj_orders (
    order_id INT, user_id INT, order_date STRING
);

INSERTINTO t16_zj_orders VALUES
(1, 1, '2025-01-05'),
(2, 2, '2025-01-10'),
(3, 1, '2025-01-15'),
(4, 3, '2025-01-20'),
(5, 1, '2025-02-03'),
(6, 2, '2025-02-10'),
(7, 4, '2025-02-15'),
(8, 3, '2025-02-20'),
(9, 1, '2025-03-05'),
(10, 2, '2025-03-10');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-06-28，如有侵权请联系 cloudcommunity@tencent.com 删除

面试