播面

面试真题：统计多终端购买用户比例

1. 题目背景

在电商、生活服务等多元化互联网业务场景中，用户可能会在不同的终端（如手机App、微信小程序、H5/Web网页）进行下单。分析跨终端的用户行为有助于评估产品多端协同的效果以及用户的忠诚度。

题目要求：
请编写 SQL 统计在过去 30 天内，同时使用过 App、微信小程序（WeChat）和 Web 网页三种终端进行过成功购买行为的用户，占同期有购买行为的总用户的比例（结果保留 4 位小数）。

2. 模拟数据

用户订单表：t_user_order_detail

假设“当前统计日期”为 2023-10-31，过去 30 天的范围是 2023-10-02 至 2023-10-31。

数据解析说明：

• 101：在30天内使用 App、WeChat、Web 成功购买，满足条件。
• 102：在30天内仅使用 App、WeChat 购买，不满足。
• 103：在30天内仅使用 Web 购买，不满足。
• 104：虽然使用过三种终端，但 App 购买时间在9月1日（超出30天范围），在30天内只算使用了 WeChat 和 Web，不满足。
• 105：在30天内使用 App、WeChat、Web 成功购买，满足条件。
• 106：支付失败（Failed），不计入购买用户。
• 分母（总购买人数）：在过去30天内有成功购买记录的去重用户，共 5 人（101, 102, 103, 104, 105）。
• 分子（目标人数）：2 人（101, 105）。
• 期望结果：$2 / 5 = 0.4000$（40%）。

3. SQL 解决方案 (基于 MySQL 8.0+ / Hive)

WITH active_users AS (
    SELECT 
        user_id,
        COUNT(DISTINCT terminal_type) AS terminal_count
    FROM t_user_order_detail
    WHERE pay_status = 'Success'
      -- 实际面试中可用 CURRENT_DATE, 此处为配合 mock 数据使用固定日期
      AND pay_time >= DATE_SUB('2023-10-31', INTERVAL 30 DAY) 
      AND terminal_type IN ('App', 'WeChat', 'Web')
    GROUP BY user_id
)
SELECT 
    ROUND(
        COUNT(CASE WHEN terminal_count = 3 THEN 1 END) * 1.0 / COUNT(1), 
        4
    ) AS multi_terminal_ratio
FROM active_users;

4. 核心考点与 SQL 剖析

1. 时间窗口过滤 (DATE_SUB / INTERVAL)

   • 考点：如何准确圈定“过去30天”的数据。
   • 解析：标准 SQL 中常使用 pay_time >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)。在面试中，千万不能漏掉这个时间过滤条件。

2. 多终端去重计数 (COUNT(DISTINCT terminal_type))

   • 考点：如何判断用户“同时”使用了三种终端。
   • 解析：在 GROUP BY user_id 的基础上，通过 COUNT(DISTINCT terminal_type) 来统计每个用户在指定时间内使用了几种终端。因为限定了 terminal_type IN ('App', 'WeChat', 'Web')，如果去重后的技术等于 3，则说明该用户这三种终端都用过。

3. 计算比例时的“整除”陷阱 (DataType Casting)

   • 考点：在很多 SQL 引擎（如 Hive, PostgreSQL, SQL Server）中，两个整数相除会直接舍弃小数部分（例如 2 / 5 结果为 0）。
   • 解析：通过将分子乘以 1.0（即 COUNT(...) * 1.0）强制将其隐式转换成浮点数，再进行除法运算，最后用 ROUND(..., 4) 保留四位小数。

4. 条件聚合 (COUNT(CASE WHEN...))

   • 考点：如何在一行查询中同时算出分子和分母。
   • 解析：active_users 临时表已经过滤出了在30天内有购买行为的全体用户。
     • 分母：COUNT(1) 计算该表中的总行数（即总购买人数）。
     • 分子：COUNT(CASE WHEN terminal_count = 3 THEN 1 END)，只有满足使用全部 3 种终端的用户才会被计入分子。

5. 面试通关技巧与拓展追问

• 追问 1：如果有新终端加入（比如支付宝小程序），你的 SQL 怎么扩展？
  • 解答：只需修改 IN ('App', 'WeChat', 'Web', 'Alipay') 并将外层的 terminal_count = 3 改为 terminal_count = 4 即可。
• 追问 2：如果数据量极大，COUNT(DISTINCT) 导致了数据倾斜，该如何优化？
  • 解答：可以先进行一次 SELECT DISTINCT user_id, terminal_type 降维去重，然后再进行 GROUP BY user_id 计数，从而避免在单节点聚合时由于 DISTINCT 带来的内存和计算压力。