数仓面试中3个常考的SQL数据分析题-22l2-技术成就未来

在数据类岗位招聘过程中，经常会考察求职者的SQL能力，这里整理了3个常考的SQL数据分析题，按照由简单到复杂排序，一起来测试一下你掌握了么？

PS：以下SQL代码在MySQL8.0及其以上版本运行。

题目1：找出每个部门工资第二高的员工

现有一张公司员工信息表employee，表中包含如下4个字段。

employee_id（员工ID）

employee_name（员工姓名）

employee_salary（员工薪资）

department（员工所属部门ID）

employee表的数据如下表所示。

还有一张部门信息表department，表中包含如下两个字段。

department_id（部门ID）

department_name（部门名称）

department表的数据如下表所示。

数据导入的代码如下：

问题：查询每个部门薪资第二高的员工信息。

输出内容包括：

employee_id（员工ID）

employee_name（员工姓名）

employee_salary（员工薪资）

department_id（员工所属部门名称）

结果样例如下图所示。

可供参考的解题思路：

涉及知识点：

本题的SQL代码如下，供读者参考：

题目2：网站登录时间间隔统计

现有一张网站登录情况表login_info，该表记录了所有用户的网站登录信息，包含如下两个字段。

user_id（用户ID）

login_time（用户登录日期）

login_info表的数据如下表所示。

数据导入的代码如下：

问题：计算每个用户登录日期间隔小于5天的次数。

输出内容包括：

user_id（用户ID）

num（用户登录日期间隔小于5天的次数）

结果样例如下图所示。

可供参考的解题思路：

SELECT user_id ,login_time ,LEAD(login_time,1) OVER (PARTITION BY user_id ORDER BY login_time) AS next_login_timeFROM login_info;

查询结果如下图所示。

在上图中可以发现，经过LEAD()函数处理后，数据会根据user_id字段分组后按照login_time字段排序。经过内层的处理后，只需在外层筛选出next_login_time与login_time字段的日期差小于5天的数据，即最终统计的目标数据，这里使用了TIMESTAMPDIFF(DAY, login_time, next_login_time)计算日期差，最后分组聚合统计不同user_id的记录个数，即每个用户登录日期间隔小于5天的次数。

涉及知识点：

本题的SQL代码如下，供读者参考：

题目3：用户购买渠道分析

现有一张用户购买信息表purchase_channel，该表记录了用户在某购物平台的购物信息，该购物平台具有网页端（web）和手机端（app）两种访问方式，表中包含如下4个字段。

user_id（用户ID）：

channel（用户购买渠道）：

purchase_date（购买日期）：

purchase_amount（购买金额）：

purchase_channel表的数据如下表所示。

数据导入代码如下：

问题：查询每天仅使用手机端的用户、仅使用网页端的用户和同时使用网页端和手机端（both）的不同用户人数和总购物金额，并且即使某天某渠道没有用户的购买信息，也需要展示。

输出内容包括：