播面

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

1. 题目描述

在电商或内容平台中，促进新用户向付费会员转化是核心运营目标之一。请编写 SparkSQL 语句，计算新注册的“普通用户”（regular）在注册后 7 天内（含注册当天，即 $0 \le t_{升级} - t_{注册} \le 7$）完成首次“Plus付费会员”升级的转化率。

2. 基础数据样例

表 1：用户注册表 user_register

记录用户的注册日期和初始用户类型。

表 2：会员升级记录表 member_upgrade

记录用户升级为各等级会员的时间（一个用户可能有多次升级/续费记录）。

3. 期望输出结果

注：

• 注册的普通用户（regular）共 5 人（101, 102, 103, 104, 105）。106 注册时即为 vip，排除。
• 7天内首次升级 Plus 的有：101（2天后）、103（3天后），共 2 人。
• 102（9天后）、105（11天后）超过7天限制，104未升级。

4. SparkSQL 实现解答

WITH first_plus_upgrade AS (
    -- 1. 过滤并获取每个用户首次升级为 Plus 会员的时间，防止多次升级数据干扰
    SELECT 
        user_id,
        MIN(CAST(upgrade_date AS DATE)) AS first_upgrade_date
    FROM 
        member_upgrade
    WHERE 
        member_level = 'Plus'
    GROUP BY 
        user_id
),
user_base AS (
    -- 2. 筛选出注册类型为普通用户的样本
    SELECT 
        user_id,
        CAST(register_date AS DATE) AS register_date
    FROM 
        user_register
    WHERE 
        user_type = 'regular'
)
-- 3. 关联并计算转化率
SELECT 
    COUNT(DISTINCT r.user_id) AS total_reg_users,
    COUNT(DISTINCT CASE 
        WHEN u.first_upgrade_date IS NOT NULL 
             AND DATEDIFF(u.first_upgrade_date, r.register_date) BETWEEN 0 AND 7 
        THEN r.user_id 
    END) AS converted_users,
    ROUND(
        COUNT(DISTINCT CASE 
            WHEN u.first_upgrade_date IS NOT NULL 
                 AND DATEDIFF(u.first_upgrade_date, r.register_date) BETWEEN 0 AND 7 
            THEN r.user_id 
        END) * 100.0 / COUNT(DISTINCT r.user_id), 
        2
    ) AS `conversion_rate_%`
FROM 
    user_base r
LEFT JOIN 
    first_plus_upgrade u 
ON 
    r.user_id = u.user_id;

5. SparkSQL 深度分析与面试通关指南

在面试中，仅仅给出上述 SQL 写法能拿到及格分，但如果能主动向面试官阐述以下设计细节和性能调优考量，将大大增加通过几率：

分析点一：为什么先做 GROUP BY MIN 聚合再 Join？

• 数据去重与防翻倍：一个用户可能会多次订购或升级 Plus 会员（如数据中的 101 号用户）。如果不先取 MIN(upgrade_date) 获得首次升级时间，直接拿注册表和升级表进行 LEFT JOIN，会导致数据发生1对多膨胀，从而计算出错误的注册用户总数（Denominator）。
• 性能优势：先在 member_upgrade 表上进行 GROUP BY 聚合，能够大幅减少参与 Join 的数据条数，降低 Shuffle 阶段的网络传输开销。

分析点二：Spark 内置日期函数与类型安全

• 代码中显式使用了 CAST(... AS DATE)，这是生产环境中的优秀实践。因为在 Hive/Spark 中，日期往往以 String 存储。显式转换能避免因格式隐式转换失败导致的 NULL 值。
• DATEDIFF(end, start) 函数计算的是 end - start 的天数差。面试中需要向面试官确认“7天内”是否包含当天（本题包含当天，故天数差判定为 BETWEEN 0 AND 7）。

分析点三：Spark 性能调优考量（主动加分项）

1. Broadcast Hash Join (广播连接)：
   • 在实际业务中，user_register 可能是百亿级大表，而 first_plus_upgrade（仅包含升级Plus的用户）数据量相对较小。
   • 如果升级表聚合后的体积小于广播阈值（默认 spark.sql.autoBroadcastJoinThreshold 为 10MB），Spark 会自动采用广播连接。
   • 面试话术：“在实际运行中，我会检查 first_plus_upgrade 过滤后的大小，如果比较小，我会考虑显式使用 BROADCAST 提示（Hint），如 LEFT JOIN /*+ BROADCAST(u) */ first_plus_upgrade u，以避免大表之间的 Shuffle，从而大幅提升计算性能。”
2. 数据倾斜处理：
   • 如果注册表存在大量 user_id 为空或异常值的情况，Join 过程中会产生严重的数据倾斜。
   • 面试话术：“为防止数据倾斜，在实际数仓建设中，我们会前置在 ODS/DWD 层过滤掉 user_id IS NULL 或异常非数字的脏数据，确保 Join Key 分布均匀。”