基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

找出过去 30 天内首次上架的新商品，统计每个新品在上架后 14 天内的累计销量、累计浏览量和加购转化率

经典数据分析 SQL 面试题：新品上市表现评估

1. 题目背景

在电商业务中，实时监控新商品的上市表现至关重要。业务部门通常需要评估新品上架初期的“爆发力”，以便及时调整库存和推广策略。

面试要求：
找出过去 30 天内（以 2023-11-01 为当天）首次上架的新商品，统计每个新品在上架后 14 天内（含上架当天）的：

  1. 累计销量（buy 行为的 quantity 总和）
  2. 累计浏览量（view 行为的总次数）
  3. 加购转化率（加购独立用户数 UV / 浏览独立用户数 UV，若浏览 UV 为 0 则输出 0）

2. 示例数据

表 1：商品维表 dim_product

该表记录了商品的上架日期。

product_id product_name launch_date status
101 iPhone 15 Pro 2023-10-15 active
102 iPad Air 6 2023-10-25 active
103 MacBook Air M3 2023-09-01 active
104 AirPods Max 2 2023-10-01 active
105 Apple Watch S9 2023-10-29 active

表 2：用户行为事实表 fact_user_behavior

该表记录了用户对商品的操作行为。

action_id user_id product_id action_type action_date quantity
1 U01 101 view 2023-10-15 NULL
2 U01 101 cart 2023-10-15 NULL
3 U01 101 buy 2023-10-16 2
4 U02 101 view 2023-10-20 NULL
5 U02 101 buy 2023-10-21 1
6 U03 101 view 2023-10-30 NULL
7 U01 102 view 2023-10-26 NULL
8 U02 102 view 2023-10-26 NULL
9 U02 102 cart 2023-10-27 NULL
10 U03 103 view 2023-09-02 NULL
11 U01 105 view 2023-10-30 NULL

3. 期望输出结果

（假设当前系统时间为 2023-11-01）

product_id product_name launch_date cumulative_sales cumulative_views cart_conversion_rate
101 iPhone 15 Pro 2023-10-15 3 2 0.5000
102 iPad Air 6 2023-10-25 0 2 0.5000
105 Apple Watch S9 2023-10-29 0 1 0.0000

结果说明：

  • 103 和 104 商品上架时间早于 2023-10-02（过去 30 天外），故排除。
  • 101 在上架后 14 天内（10-15 至 10-28），行为 ID 6 发生在 10-30 属于 14 天外，被排除。期间 view 的 UV 是 2 (U01, U02)，cart 的 UV 是 1 (U01)，转化率为 1/2 = 0.5。销量为 2+1=3。

4. SQL 解答

sql 
WITH new_products AS (
    -- 1. 筛选出过去 30 天内首次上架的新商品
    -- 生产环境通常使用 CURRENT_DATE()，此处用 '2023-11-01' 模拟当天
    SELECT 
        product_id, 
        product_name, 
        launch_date
    FROM dim_product
    WHERE launch_date >= DATE_SUB('2023-11-01', INTERVAL 30 DAY)
      AND launch_date <= '2023-11-01'
)
SELECT 
    p.product_id,
    p.product_name,
    p.launch_date,
    -- 2. 统计 14 天内累计销量
    COALESCE(SUM(CASE WHEN b.action_type = 'buy' THEN b.quantity ELSE 0 END), 0) AS cumulative_sales,
    -- 3. 统计 14 天内累计浏览量（PV）
    COALESCE(SUM(CASE WHEN b.action_type = 'view' THEN 1 ELSE 0 END), 0) AS cumulative_views,
    -- 4. 计算加购转化率（加购UV / 浏览UV）
    ROUND(
        COALESCE(
            COUNT(DISTINCT CASE WHEN b.action_type = 'cart' THEN b.user_id END) * 1.0 
            / NULLIF(COUNT(DISTINCT CASE WHEN b.action_type = 'view' THEN b.user_id END), 0),
            0
        ), 4
    ) AS cart_conversion_rate
FROM new_products p
LEFT JOIN fact_user_behavior b
  ON p.product_id = b.product_id
 -- 5. 限制行为必须发生在上架后的 14 天内（含当天）
 AND b.action_date >= p.launch_date
 AND b.action_date <= DATE_ADD(p.launch_date, INTERVAL 13 DAY)
GROUP BY 
    p.product_id, 
    p.product_name, 
    p.launch_date
ORDER BY 
    p.product_id;

5. 面试官视角：SQL 深度分析与答题技巧

此题看似基础，但在大厂面试中含金量极高，主要考察候选人处理“动态时间窗口”、“数据倾斜与清洗”以及“指标健壮性”的能力。

① 核心考点 1：动态关联条件的运用 (ON vs WHERE)

  • 误区：新手常将 b.action_date <= DATE_ADD(...) 的过滤条件写在 WHERE 子句中。
  • 痛点：若写在 WHERE 中，一旦某个新商品在上架 14 天内没有任何用户行为（如上面的 105 ），在 LEFT JOIN 后这些记录会因为 WHERE 的过滤而被彻底排除。
  • 解题技巧：必须把时间窗口限制条件写在 LEFT JOIN 的 ON 子句中。这样能确保即使没有行为数据，新品依然会保留在结果集中，且各项指标显示为 0 或 NULL。

② 核心考点 2：指标口径的精准还原 (转化率算法)

  • 转化率定义：电商中“转化率”通常分为 UV 转化率（人数）和 PV 转化率（次数）。
    • 本题要求：加购人数 / 浏览人数 (UV 转化)。因此使用 COUNT(DISTINCT user_id)。
    • 注意条件嵌套：COUNT(DISTINCT CASE WHEN action_type = 'cart' THEN user_id END) 可以精准提取出有过加购行为的独立用户数。

③ 核心考点 3：防错设计（除以 0 风险与 NULL 值处理）

  • 除零错误 (Division by zero)：如果某个新品只有加购，或者根本没有浏览量，直接相除会导致 SQL 报错崩溃。
  • 解决方案：
    1. 使用 NULLIF(expression, 0)：当浏览 UV 为 0 时，将其转换为 NULL。
    2. 任何数除以 NULL 都会得到 NULL，最后通过 COALESCE(..., 0) 将 NULL 转换为 0，保证了代码在任何极端数据下的健壮性。

④ 面试加分项：大表关联优化思维

如果面试官追问：“如果 fact_user_behavior 有百亿级数据，这个 SQL 该怎么优化？”

  • 追问回答方向：
    1. 分区裁剪：用户行为表通常按天分区。由于新商品最长只上架 30 天，且我们只关心上架后 14 天的数据，因此行为表最多只需要扫描过去 44 天的数据（30天前上架 + 14天观察期）。可以在 JOIN 前，对 fact_user_behavior 进行分区过滤（WHERE action_date >= DATE_SUB(..., 44)）。
    2. 避免笛卡尔积：先在子查询中对行为表按 product_id 进行轻度聚合（过滤出 14 天内的行为），然后再与新品表进行 LEFT JOIN。
00:00
00:00