数据分析师助理面试通关指南

⚡ SQL核心题型与参考答案

题1：用户首次购买分析
表结构：orders（user_id, order_id, order_date, amount）
问题：统计每个用户首次订单的金额及时间，计算整体平均首次订单金额。
参考答案：

SELECT AVG(first_amount) AS avg_first_amount
FROM (
  SELECT user_id, MIN(order_date) AS first_date,
         FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS first_amount
  FROM orders
) t
GROUP BY user_id;

题2：漏斗转化率计算
表结构：events（user_id, event_type, event_time）
事件类型：page_view, add_to_cart, purchase
问题：计算从浏览到加购、加购到购买的转化率。
参考答案：

SELECT
  COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN user_id END) AS pv_users,
  COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN user_id END) AS cart_users,
  COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS buy_users,
  ROUND(COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN user_id END) * 100.0 /
        COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN user_id END), 2) AS pv_to_cart_rate,
  ROUND(COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) * 100.0 /
        COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN user_id END), 2) AS cart_to_buy_rate
FROM events;

题3：连续活跃用户
表结构：user_logs（user_id, login_date）
问题：找出连续3天及以上登录的用户。
参考答案（使用窗口函数差值法）：

WITH ranked AS (
  SELECT user_id, login_date,
         DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM user_logs
  GROUP BY user_id, login_date  -- 去重
)
SELECT DISTINCT user_id
FROM ranked
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

⚙️ Excel高频函数与技巧

题1：去重计数
数据区域A2:A100包含重复ID，要求统计唯一ID数量。
解法1：数据透视表（拖入字段→值设置为“计数”→筛选重复项）
解法2：公式法（=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))）
注意：Excel 365可用UNIQUE+COUNTA组合

题2：多条件匹配
左表：订单号、产品ID；右表：产品ID、价格、库存
要求：返回对应价格与库存
解法：XLOOKUP(A2, $F$2:$F$100, $G$2:$G$100) & XLOOKUP(A2, $F$2:$F$100, $H$2:$H$100)
注意：避免VLOOKUP的列序号易错问题；若需模糊匹配可用TRUE参数

题3：动态筛选
需求：按部门筛选员工数据，部门列表需动态更新
解法：数据验证→序列（=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)）+筛选器
高阶：Power Query导入数据→分组聚合→参数化筛选

📊 统计学基础概念与应用

题1：p值含义
问题：在A/B测试中，p=0.03代表什么？能否认为效果显著？
参考答案：p值是在原假设成立的前提下，观察到当前结果或更极端结果的概率。p<0.05时拒绝原假设，但需注意样本量影响——大样本下微小差异也可能显著，需结合效应量（如Cohen's d）判断实际意义。

题2：相关性陷阱
问题：发现“冰淇淋销量”与“溺水人数”高度正相关（r=0.8），能否得出“吃冰淇淋导致溺水”？
参考答案：不能！这是典型的混杂变量干扰（夏季高温同时推高两者）。应通过多元回归控制温度变量，或设计随机对照实验。

🛍️ 电商/金融典型业务场景分析

题1：新用户流失分析
关键步骤：
① 定义流失：30天未活跃（需区分新老用户）
② 分层对比：新用户 vs 老用户流失率差异
③ 行为归因：流失用户最后一次使用功能（如未完成支付、跳过引导页）
④ 实验验证：对流失用户推送优惠券，观察回流率提升
⑤ 建议方案：优化新手引导流程、设置7日召回提醒

题2：促销活动效果评估
易错点：
✘ 仅比较活动前后GMV
✓ 应设计对照组（未参与活动的相似用户）
✓ 计算增量GMV = 活动组GMV - 对照组GMV
✓ 分析用户分层：新客拉新 vs 老客复购贡献
✓ 关注长期影响：活动后30天留存率是否下降（过度优惠导致价格敏感）

🤝 行为面试题：STAR法则实战模板

题1：如何处理数据争议？
Situation：运营认为用户数下降10%，但技术报表显示仅降3%
Task：作为数据分析师需定位差异原因
Action：① 检查统计口径（DAU定义是否含测试账号）② 核对数据源（运营用后台API，我用日志库）③ 发现运营未过滤机器人流量
Result：统一口径后达成一致，推动建立指标字典文档

题2：如何应对紧急需求？
Situation：大促前2小时业务方要求临时增加实时监控看板
Task：在有限时间内保证数据准确性
Action：① 优先使用已上线的Hive表（避免实时任务风险）② 复用现有SQL模板，仅替换时间参数③ 用Excel做交叉验证
Result：1小时内交付，业务方确认准确，后续沉淀为标准模板