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

一、题目背景

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

二、题目

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

t16_zj_orders 表

代码语言：javascript

复制

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

代码语言：javascript

复制

SELECTuser_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 ELSE1ENDAS is_repurchase --0: 首单，1: 复购单FROM t16_zj_orders;

执行结果

代码语言：javascript

复制

---------- ----------- ------------- ----- ---------------- | 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：按月聚合算复购率

代码语言：javascript

复制

SELECTmonth,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_rateFROM (SELECTDATE_FORMAT(order_date, 'yyyy-MM') ASmonth,user_id,CASEWHEN ROW_NUMBER() OVER (PARTITIONBY user_id ORDERBY order_date) > 1THEN1ELSE0ENDAS is_repurchaseFROM t16_zj_orders) tGROUPBYmonthORDERBYmonth

最终结果：

代码语言：javascript

复制

---------- -------------- ------------------- ------------------ |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) 统计复购用户数

八、建表语句和数据插入

代码语言：javascript

复制

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，如有侵权请联系[email protected] 删除