在数据驱动的时代，SQL技能无疑是数据分析师的必备利器。今天，我们为大家整理了3道常考的SQL数据分析题，从基础到高级，让你在面试中游刃有余！

文章首发于公众号【数据分析面试】，快来领取你的专属面试宝典吧！

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

首先，让我们来解答第一个问题：如何找出每个部门薪资第二高的员工？

解题思路：

1. 使用窗口函数`RANK()`按部门ID分组，并按薪资降序排列。2. 将处理后的表与部门信息表进行内连接，获取部门名称。3. 在连接后的表上筛选出薪资排名第二的员工。

SQL代码：

sqlSELECT a.employee_id, a.employee_name, a.employee_salary, b.department_idFROM (    SELECT *, RANK() OVER (PARTITION BY department ORDER BY employee_salary DESC) AS ranking    FROM employee) AS aINNER JOIN department AS b ON a.department = b.department_idWHERE a.ranking = 2;

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

接下来，我们来看第二个问题：如何计算每个用户登录日期间隔小于5天的次数？

解题思路：

1. 利用`LEAD()`函数获取每个用户的下一次登录日期。2. 计算相邻两次登录日期的差值，并筛选出小于5天的记录。3. 按用户ID分组，统计符合条件的记录数。

SQL代码：

sqlSELECT a.user_id, COUNT(*) AS numFROM (    SELECT user_id, login_time, LEAD(login_time, 1) OVER (PARTITION BY user_id ORDER BY login_time) AS next_login_time    FROM login_info) AS aWHERE TIMESTAMPDIFF(DAY, login_time, next_login_time) < 5GROUP BY user_id;

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

最后，我们来解决第三个问题：如何查询每天仅使用手机端的用户、仅使用网页端的用户和同时使用网页端和手机端的不同用户人数和总购物金额？

解题思路：

1. 根据用户ID和日期进行分组，统计各用户在各购买渠道的购物记录。2. 使用`UNION`将仅使用网页端和仅使用手机端的用户数据合并。3. 在外部查询中，统计不同购买日期、购买渠道的总购买金额和总购买用户。

SQL代码：

sqlSELECT t1.purchase_date, t1.channel, t2.sum_amount, t2.total_usersFROM (    SELECT DISTINCT purchase_date, MIN(channel) AS channel    FROM purchase_channel    GROUP BY purchase_date, user_id    HAVING COUNT(DISTINCT channel) = 1    UNION    SELECT purchase_date, 'both' AS channel    FROM purchase_channel    GROUP BY purchase_date, user_id    HAVING COUNT(DISTINCT channel) > 1) AS t1LEFT JOIN (    SELECT purchase_date, channel, SUM(sum_amount) AS sum_amount, SUM(total_users) AS total_users    FROM (        SELECT purchase_date, MIN(channel) AS channel, SUM(purchase_amount) AS sum_amount, COUNT(DISTINCT user_id) AS total_users        FROM purchase_channel        GROUP BY purchase_date, user_id        HAVING COUNT(DISTINCT channel) = 1        UNION ALL        SELECT purchase_date, 'both' AS channel, SUM(purchase_amount) AS sum_amount, COUNT(DISTINCT user_id) AS total_users        FROM purchase_channel        GROUP BY purchase_date, user_id        HAVING COUNT(DISTINCT channel) > 1    ) AS c    GROUP BY purchase_date, channel) AS t2 ON t1.purchase_date = t2.purchase_date AND t1.channel = t2.channel;

这些题目涵盖了SQL数据分析的多个方面，希望你能从中受益。如果你觉得这篇文章对你有帮助，请记得点赞、分享和关注哦！

文章首发于公众号【数据分析面试】，更多面试技巧等你来发现！