面试题目：计算新注册普通用户7天内升级Plus会员的转化率

1. 背景描述

在电商或内容平台中，新用户的首周转化黄金期至关重要。请根据给定的用户注册表和会员升级记录表，计算新注册普通用户在注册后 7 天内（即 $[reg\_time, reg\_time + 7\text{ days}]$ 闭区间内）完成首次升级为“Plus付费会员”的转化率。

2. 示例数据

表1：用户注册表 (users)

记录所有新注册用户的信息。

表2：会员升级记录表 (member_upgrades)

记录用户升级会员的操作日志。同一用户可能存在多次升级记录。

数据分析说明：

• 101: 10-01注册，10-05升级Plus（4天内，分子+1）
• 102: 10-01注册，10-10升级Plus（9天，不符合）
• 103: 10-02注册，未升级（不符合）
• 104: 10-03注册，10-04升级Plus（1天内，分子+1）
• 105: 10-05注册，先升级Silver后在10-07升级Plus（2天内，分子+1）
• 106: 10-06注册，未升级（不符合）
• 预期转化率 = 符合条件的付费用户数 (3) / 总注册普通用户数 (6) = 50.00%

3. 考察考点

1. 左连接（LEFT JOIN）的运用：保留所有分母用户，防止未升级的用户在连接中丢失。
2. 子查询/CTE 的使用：如何筛选出用户“首次”升级到“Plus”的时间，避免历史多次升级记录导致的数据翻倍。
3. 时间差值计算：不同 SQL 数据库中对时间间隔的处理（如 INTERVAL 或是时间函数）。
4. 聚合与条件分支：利用 COUNT(DISTINCT ...) 或 SUM(CASE WHEN ...) 计算转化率并处理分母为 0 的情况。

4. SQL 实现 (基于 MySQL 标准语法)

WITH first_plus_upgrade AS (
    -- 1. 筛选出每个用户首次升级为 Plus 会员的时间
    SELECT 
        user_id,
        MIN(upgrade_time) AS first_plus_time
    FROM 
        member_upgrades
    WHERE 
        target_level = 'Plus'
    GROUP BY 
        user_id
)

-- 2. 计算转化率
SELECT 
    COUNT(u.user_id) AS total_reg_users,
    COUNT(f.user_id) AS converted_users_within_7_days,
    -- 用 ROUND 格式化为百分比形式
    ROUND(
        COUNT(CASE WHEN f.first_plus_time <= DATE_ADD(u.reg_time, INTERVAL 7 DAY) THEN 1 END) 
        * 100.0 / COUNT(u.user_id), 
        2
    ) AS conversion_rate_percent
FROM 
    users u
LEFT JOIN 
    first_plus_upgrade f ON u.user_id = f.user_id;

5. 核心逻辑分析

第一步：提取“首次Plus升级时间”

在 member_upgrades 中，用户 105 有两次升级记录。如果我们直接做 JOIN，会产生多条记录导致分母膨胀。因此，必须先通过 WITH 建立 CTE (公共表表达式) first_plus_upgrade，利用 MIN(upgrade_time) 找出每个用户第一次升级为 Plus 的确切时间，确保每个用户在子查询中只有唯一的一行。

第二步：保留全体注册用户

使用 LEFT JOIN 将 users 表（主表）与 first_plus_upgrade（附表）连接。这样能确保即使未升级的用户（如103、106）也能保留在结果集内，它们对应的 first_plus_time 为 NULL。

第三步：时间窗口过滤与条件计数

通过 CASE WHEN f.first_plus_time <= DATE_ADD(u.reg_time, INTERVAL 7 DAY) THEN 1 END 严格控制“7天内”这个业务场景。

• 如果满足：注册时间 + 7天 $\ge$ 首次升级时间，则返回 1。
• 如果不满足或为 NULL，则返回 NULL。
• 最后利用 COUNT(...) 函数（会自动忽略 NULL 值）计算出通过转换的分子。

6. 面试加分项与扩展应对

Q1: 为什么要用 MIN(upgrade_time)，直接连表不行吗？

答：如果直接连表，当用户在7天内有多次升级 Plus 记录，或者后续有多次降级再升级记录时，LEFT JOIN 会产生多条重复数据，导致 COUNT(u.user_id)（分母）虚高，计算出的转化率偏低。先聚合求 MIN 可以保证数据的幂等性和准确性。

Q2: 如果是在 Hive/Spark SQL 环境下，时间函数要怎么改写？

答：Hive 中不支持 DATE_ADD(col, INTERVAL 7 DAY) 这种语法。可以改写为：

• datediff(f.first_plus_time, u.reg_time) <= 7 或者是：
• f.first_plus_time <= date_add(u.reg_time, 7)

Q3: 如果存在同一秒内注册和升级，或者注册时间晚于升级时间的异常数据怎么处理？

答：在实际数仓中，可能会存在系统延迟导致的“先升级后注册”的脏数据。可以在 CASE WHEN 中加入安全边界，即 f.first_plus_time BETWEEN u.reg_time AND DATE_ADD(u.reg_time, INTERVAL 7 DAY)，确保时间逻辑的合理性。