MySql场景面试题：电商业务中用户的举动路径和转化率分析 ...

❃博主首页 ：   「码到三十五」   ，同名公众号 :「码到三十五」，wx号 : 「liwu0213」   
  ☠博主专栏 ：   <mysql高手>    <elasticsearch高手>    <源码解读>    <java核心>    <面试攻关>   
  ♝博主的话 ：  搬的每块砖，皆为峰峦之基；公众号搜刮「码到三十五」关注这个爱发技术干货的coder，一起筑基   

---

电商业务,一个订单表 orders 和一个用户表 users。现必要分析用户的举动路径和转化率。
表结构：

• 用户表 (users):
  
  
  
  • user_id (用户ID, 主键)
    
  • register_date (注册日期)
    
  • city (所在都会)
    
  
  
• 订单表 (orders):
  
  
  
  • order_id (订单ID, 主键)
    
  • user_id (用户ID, 外键)
    
  • order_date (订单日期)
    
  • amount (订单金额)
    
  
  

详细需求如下：

• 用户举动路径分析：
  
  
  
  • 统计用户在2023年内的举动路径，包括：
    
    
    
    • 初次购买日期。
      
    • 第二次购买日期（如果有）。
      
    • 最后一次购买日期。
      
    • 购买次数。
      
    • 累计购买金额。
      
    
    
  • 按都会分组，统计每个都会的用户举动路径。
    
  
  
• 转化率分析：
  
  
  
  • 计算以下转化率：
    
    
    
    • 注册到初次购买转化率：注册用户中在2023年内完成初次购买的用户占比。
      
    • 初次购买到第二次购买转化率：初次购买用户中完成第二次购买的用户占比。
      
    • 复购用户到高代价用户转化率：复购用户中累计购买金额 ≥ 10,000 元的用户占比。
      
    
    
  
  
• 用户留存分析：
  
  
  
  • 计算用户在初次购买后的第30天、60天、90天的留存率。
    
  • 按都会分组，统计每个都会的用户留存率。
    
  
  

---

参考SQL

1. WITH user_orders_2023 AS (
2.     -- 获取2023年内的订单数据，并标记首次、第二次、最后一次购买日期
3.     SELECT
4.         u.user_id,
5.         u.city,
6.         u.register_date,
7.         o.order_date,
8.         o.amount,
9.         MIN(o.order_date) OVER (PARTITION BY o.user_id) AS first_order_date,
10.         LEAD(o.order_date, 1) OVER (PARTITION BY o.user_id ORDER BY o.order_date) AS second_order_date,
11.         MAX(o.order_date) OVER (PARTITION BY o.user_id) AS last_order_date,
12.         COUNT(o.order_id) OVER (PARTITION BY o.user_id) AS order_count,
13.         SUM(o.amount) OVER (PARTITION BY o.user_id) AS total_amount
14.     FROM
15.         users u
16.     JOIN
17.         orders o ON u.user_id = o.user_id
18.     WHERE
19.         o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
20. ),
21. user_behavior_path AS (
22.     -- 统计用户行为路径
23.     SELECT
24.         user_id,
25.         city,
26.         first_order_date,
27.         second_order_date,
28.         last_order_date,
29.         order_count,
30.         total_amount
31.     FROM
32.         user_orders_2023
33.     GROUP BY
34.         user_id, city, first_order_date, second_order_date, last_order_date, order_count, total_amount
35. ),
36. conversion_analysis AS (
37.     -- 计算转化率
38.     SELECT
39.         city,
40.         COUNT(DISTINCT CASE WHEN first_order_date IS NOT NULL THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS registration_to_first_purchase,
41.         COUNT(DISTINCT CASE WHEN second_order_date IS NOT NULL THEN user_id END) * 1.0 / COUNT(DISTINCT CASE WHEN first_order_date IS NOT NULL THEN user_id END) AS first_to_second_purchase,
42.         COUNT(DISTINCT CASE WHEN total_amount >= 10000 THEN user_id END) * 1.0 / COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) AS repurchase_to_high_value
43.     FROM
44.         user_orders_2023
45.     GROUP BY
46.         city
47. ),
48. retention_analysis AS (
49.     -- 计算用户留存率
50.     SELECT
51.         city,
52.         COUNT(DISTINCT CASE WHEN DATEDIFF(order_date, first_order_date) >= 30 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS retention_30d,
53.         COUNT(DISTINCT CASE WHEN DATEDIFF(order_date, first_order_date) >= 60 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS retention_60d,
54.         COUNT(DISTINCT CASE WHEN DATEDIFF(order_date, first_order_date) >= 90 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS retention_90d
55.     FROM
56.         user_orders_2023
57.     GROUP BY
58.         city
59. )
60. -- 综合输出：按城市输出用户行为路径、转化率和留存率
61. SELECT
62.     ubp.city,
63.     AVG(DATEDIFF(second_order_date, first_order_date)) AS avg_days_first_to_second,
64.     AVG(DATEDIFF(last_order_date, first_order_date)) AS avg_days_first_to_last,
65.     AVG(order_count) AS avg_order_count,
66.     AVG(total_amount) AS avg_total_amount,
67.     ca.registration_to_first_purchase,
68.     ca.first_to_second_purchase,
69.     ca.repurchase_to_high_value,
70.     ra.retention_30d,
71.     ra.retention_60d,
72.     ra.retention_90d
73. FROM
74.     user_behavior_path ubp
75. JOIN
76.     conversion_analysis ca ON ubp.city = ca.city
77. JOIN
78.     retention_analysis ra ON ubp.city = ra.city
79. GROUP BY
80.     ubp.city,
81.     ca.registration_to_first_purchase,
82.     ca.first_to_second_purchase,
83.     ca.repurchase_to_high_value,
84.     ra.retention_30d,
85.     ra.retention_60d,
86.     ra.retention_90d;

复制代码

---

查询逻辑分解

• user_orders_2023:
  
  
  
  • 获取2023年内的订单数据，并利用窗口函数标记每个用户的初次、第二次和最后一次购买日期，以及购买次数和累计金额。
    
  
  
• user_behavior_path:
  
  
  
  • 统计每个用户的举动路径，包括初次购买日期、第二次购买日期、最后一次购买日期、购买次数和累计金额。
    
  
  
• conversion_analysis:
  
  
  
  • 计算以下转化率：
    
    
    
    • 注册到初次购买转化率。
      
    • 初次购买到第二次购买转化率。
      
    • 复购用户到高代价用户转化率。
      
    
    
  
  
• retention_analysis:
  
  
  
  • 计算用户在初次购买后的第30天、60天、90天的留存率。
    
  
  
• 综合输出:
  
  
  
  • 将用户举动路径、转化率和留存率结合，按都会输出最终结果。
    
  
  

---

示例输出

cityavg_days_first_to_secondavg_days_first_to_lastavg_order_countavg_total_amountregistration_to_first_purchasefirst_to_second_purchaserepurchase_to_high_valueretention_30dretention_60dretention_90d北京45.67180.504.285000.800.600.250.500.400.30上海50.00200.003.878000.750.550.200.450.350.25

---

考察点

• 举动路径分析：利用窗口函数（如 MIN、LEAD、MAX）标记用户的关键举动节点。
  
• 转化率计算：通过条件聚合计算多级转化率。
  
• 留存率计算：利用 DATEDIFF 和条件聚合计算用户留存率。
  
• 多步骤数据处置惩罚：通过 WITH 子句分步骤处置惩罚数据，提升可读性和性能。
  

---

---

    关注公众号[码到三十五]获取更多技术干货 !   

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。