播面

面试题：每日新老用户销售额占比统计

1. 题目背景

在电商、零售等业务场景中，分析每日销售额的构成是极其重要的运营指标。通过监控“新用户”（当天注册且当天消费）与“老用户”（非当天注册但当天有消费）的销售额占比，可以评估获客活动的效果以及老用户的留存与复购活力。

请编写 SQL 统计每日的销售总额，以及新用户、老用户各自的金额和占比（占比保留两位小数，并展示为百分数形式，如 50.00%）。

2. 示例数据

表1：用户注册表 users

表2：订单销售表 orders

3. 期望输出结果

4. SQL 编写

SELECT 
    o.order_date,
    -- 1. 计算每日总销售额
    SUM(o.amount) AS total_amount,
    
    -- 2. 分别计算新老用户销售额
    SUM(CASE WHEN o.order_date = u.register_date THEN o.amount ELSE 0 END) AS new_user_amount,
    SUM(CASE WHEN o.order_date > u.register_date THEN o.amount ELSE 0 END) AS old_user_amount,
    
    -- 3. 计算新老用户占比，转化为百分比格式
    CONCAT(
        ROUND(
            SUM(CASE WHEN o.order_date = u.register_date THEN o.amount ELSE 0 END) / SUM(o.amount) * 100, 
            2
        ), 
        '%'
    ) AS new_ratio,
    
    CONCAT(
        ROUND(
            SUM(CASE WHEN o.order_date > u.register_date THEN o.amount ELSE 0 END) / SUM(o.amount) * 100, 
            2
        ), 
        '%'
    ) AS old_ratio

FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
GROUP BY o.order_date
ORDER BY o.order_date;

5. 核心考点与 SQL 分析

考点一：多表关联（Join）与维度对齐

• 分析：要判断用户是否是“当天注册且当天消费”，需要将销售数据和用户注册数据结合。这里以订单表 orders 为主表，通过 user_id 关联 users 表，从而获取每个下单用户的注册日期（register_date）。
• 面试提分点：使用 LEFT JOIN 而不是 INNER JOIN 可以防止部分缺失注册信息的异常用户导致订单数据丢失，在实际数仓中更加严谨。

考点二：新老用户口径的逻辑定义（Conditional Aggregation）

• 新用户条件：订单日期等于注册日期（o.order_date = u.register_date）。
• 老用户条件：订单日期大于注册日期（o.order_date > u.register_date）。
• 分析：使用 CASE WHEN 表达式实现条件聚合。当满足对应条件时返回订单金额，否则返回 0。这种方式可以在一次分组（GROUP BY）中同时计算出新老用户的销售额，避免了多次子查询或 UNION，执行效率极高。

考点三：数值计算、精度保留与格式化

• 除法防报错：实际业务中需注意分母为0的情况（虽然每日总额为0通常没有订单数据，但在更复杂的聚合中常用 NULLIF(SUM(o.amount), 0) 来防止报错）。
• 格式化函数：
  • ROUND(..., 2)：用于保留 2 位小数。
  • CONCAT(..., '%')：用于拼接百分号。在 Hive 或 MySQL 中，此操作会将数值转换为字符串并展示为易读的指标。