数据分析师岗位技能考核真题

一、SQL技能考核SQL作为数据分析师抽取、清洗、聚合数据的核心工具，其重要性不言而喻。本部分旨在考察候选人对SQL基础语法、复杂查询以及性能优化的掌握程度。真题1：基础查询与数据聚合背景：现有电商平台两张核心表：`orders`（订单表）和`users`（用户表）。`orders`表结构：`order_id`,`user_id`,`order_amount`,`order_status`(1:待付款,2:已付款,3:已发货,4:已完成,5:已取消),`create_time``users`表结构：`user_id`,`register_time`,`gender`,`city`问题：1.请写出SQL，查询2023年每个月（按自然月）的总订单数、总成交金额（仅包含已完成状态订单）、平均订单金额（仅包含已完成状态订单）。2.基于上一题结果，请进一步筛选出平均订单金额大于该年总平均订单金额的月份。解析：本题主要考察日期函数的运用、条件筛选、聚合函数以及子查询或CTE的使用。对于问题1，关键点在于使用`DATE_FORMAT`（或数据库对应函数）提取年份和月份，并对`order_status`进行过滤。对于问题2，需要先计算全年总平均订单金额，再与各月平均值比较，这里可以使用子查询将问题1的结果作为临时表，或使用窗口函数进行全局聚合。真题2：复杂逻辑与窗口函数应用背景：沿用上述`orders`表。问题：请写出SQL，查询每个用户的首单（按`create_time`最早）订单ID、首单金额以及首单后的第二笔订单金额（若用户仅有一笔订单，则第二笔订单金额为NULL）。解析：本题重点考察窗口函数（如`ROW_NUMBER()`）的灵活运用，以及自连接或条件聚合的技巧。首先，需要为每个用户的订单按时间排序，标记订单序号。然后，通过筛选序号为1和2的记录，并利用自连接或`CASEWHEN`结合聚合函数，将首单和第二单的信息匹配到同一行。二、Excel/GoogleSheets技能考核尽管高级工具层出不穷，Excel/GoogleSheets因其便捷性，仍是数据分析师日常工作中处理中小型数据集、快速探索数据和制作初步报表的重要工具。真题：数据透视表与函数综合应用背景：提供一份某产品2023年各地区（华北、华东、华南、华西）各月份的销售数据Excel表，包含“日期”、“地区”、“产品类别”、“销售额”、“利润”字段。问题：1.请使用数据透视表功能，统计各地区在不同产品类别下的总销售额与总利润，并按总销售额降序排列。2.基于上述数据透视表结果，计算每个地区每个产品类别的利润率（利润/销售额），并筛选出利润率高于20%的记录。3.请写出一个Excel函数，用于判断“销售额”列中，数值大于该列平均值的单元格标记为“高”，否则标记为“低”。解析：本题考察Excel的实际操作能力。数据透视表的行、列、值设置是基础。计算字段（利润率）的添加是关键一步。第三问则考察对`IF`函数和`AVERAGE`函数嵌套使用的掌握。三、统计分析与思维能力数据分析师需要具备基本的统计知识，并能将其应用于实际业务问题的分析。真题1：概念理解与应用问题：1.请简述什么是P值（P-value）？在A/B测试中，当我们说“结果显著”时，通常P值的阈值是多少？这意味着什么？2.某电商平台欲测试新首页对用户转化率的影响。A组（对照组）使用旧首页，转化率为3%；B组（实验组）使用新首页，转化率为4%。请问能否直接得出新首页更优的结论？为什么？如果不能，还需要哪些信息或进行哪些步骤才能判断？解析：第一问考察对统计学基本概念的理解。P值是在原假设为真的前提下，观察到当前样本结果或更极端结果出现的概率。A/B测试中常用的P值阈值是0.05，意味着当P值小于0.05时，我们有理由拒绝原假设，认为差异不是由随机因素引起的，即“结果显著”。第二问考察批判性思维。不能直接得出结论，因为转化率的差异可能由随机波动引起。需要知道样本量、是否满足统计独立性、使用何种统计检验方法（如卡方检验、比例的Z检验）以及计算出的P值等信息，才能进行科学判断。真题2：业务数据分析背景：某在线教育App近期用户留存率出现下滑，运营团队希望数据分析师找出可能的原因。问题：请阐述你会从哪些维度入手分析留存率下滑的原因？可能用到哪些数据指标？简述你的分析思路。解析：本题考察结构化分析问题的能力和业务敏感度。分析思路应遵循“宏观到微观，整体到局部”的原则。可能的维度包括：时间维度（环比、同比，确认下滑趋势是否突然）、用户维度（新老用户、用户画像、渠道来源、注册时间）、行为维度（核心功能使用频率、关键路径转化率、课程完成情况）、产品维度（近期是否有版本更新、功能调整、Bug反馈）、外部因素（行业环境、竞争对手动态、节假日等）。可能用到的指标：次日留存、7日留存、30日留存、各渠道新用户留存、关键行为（如观看课程时长、完成练习数）的用户占比及留存率等。通过对比分析、漏斗分析、分群分析等方法定位问题。四、数据可视化能力将复杂的数据以清晰、直观的图表形式呈现，是数据分析师传递信息的重要技能。真题背景：你手中有一份某公司2023年各季度、各产品线（A,B,C,D）的销售额数据。问题：1.如果要向管理层展示各产品线全年销售额的总体分布以及各季度的销售趋势，你会选择哪种或哪些图表类型？为什么？2.请简述在制作数据可视化图表时，有哪些基本原则或注意事项？解析：第一问考察对图表类型选择的理解。可以选择组合图表，例如使用堆叠柱状图展示各季度内不同产品线的销售额构成（体现分布），同时用折线图叠加展示各产品线的季度销售趋势（体现趋势）。或者，也可以考虑使用面积图。关键在于清晰传达“分布”和“趋势”两个核心信息。第二问考察可视化素养。基本原则包括：突出核心信息、选择合适的图表类型、保持简洁易懂、避免图表欺诈（如截断Y轴误导）、使用恰当的颜色和标签、添加必要的注释说明等。五、综合案例分析真题背景：某连锁咖啡店计划在新城区开设一家新店，需要你作为数据分析师提供选址建议。现有以下数据可供使用：1.新城区各商圈的人口密度、平均收入水平、年龄分布数据。2.新城区各商圈现有竞争对手（其他咖啡店）的数量、位置、大致客流量数据。3.公司现有门店在不同类型商圈的历史销售数据、客单价、利润率数据。4.新城区的交通便利性数据（如地铁站点分布、公交线路、停车场数量）。问题：请详细阐述你将如何利用这些数据进行分析，最终给出选址建议。你的分析步骤、关键指标和决策依据是什么？解析：本题是对数据分析师综合能力的考察，包括数据理解、业务建模、多维度分析和决策支持能力。分析步骤可能包括：1.数据理解与预处理：明确各数据字段含义，检查数据质量。2.商圈特征分析：对各商圈的人口、收入、年龄等进行画像，识别潜在目标客群匹配度高的区域。3.竞争格局分析：评估各商圈竞争激烈程度，分析竞争对手的优劣势。4.类比分析：将新城区商圈与现有成功门店的商圈特征进行对比，寻找相似性高的区域。5.关键指标构建与加权评分：例如，将人口密度、收入水平、目标客群占比、竞争饱和度、交通便利性等指标赋予权重，对各商圈进行综合评分。6.风险与机会评估：考虑潜在风险（如未来规划变动）和增长机会。7.结论与建议：基于综合评分和定性分析，推荐