面向经验丰富的专业人员的 SQL 面试问题

介绍

 
如果您是一位正在寻找工作的经验丰富的数据科学家，那么您就没有比这更好的时机了。 截至目前，许多成熟的组织都在寻找了解自己手艺的数据科学家。 但是，高需求并不意味着您可以或应该跳过箍并申请没有特定技能的高级职位。 在雇用经验丰富的数据科学家时，公司希望他们能够处理手头最困难的任务。 这些员工应该很好地掌握即使是最晦涩的功能，以便他们可以在必要时使用它们。

在面试高级职位时，经验丰富的数据科学家往往会被问到更难的问题，这不足为奇。 通常，在从事一项工作几年后，数据科学家变得非常擅长执行某些重复性任务。 专业人员必须认识到 SQL 不会停止并以他们现有的知识结束。 当涉及到高级 SQL 概念时，他们的知识可能仍然存在一些差距。 因此，在您的数据科学家面试中获得成功并没有什么坏处。 

SQL 是管理数据库的主要语言，因此执行 SQL 操作是数据科学家工作的核心。 大多数数据科学家的面试都是为了确定候选人的 SQL 知识。 

日常工作可能不包括编写复杂的查询，但您必须证明，如果需要这些技能，您就是有能力的人。 所以面试官会问各种各样的问题也就不足为奇了 SQL面试题 测试候选人的 SQL 流利程度。

在本文中，我们想总结一些在采访经验丰富的专业人士时提出的复杂问题和概念。 即使您对自己的 SQL 知识有信心，扫描关键字并确保您已涵盖所有内容也没有什么坏处。

经验丰富的专业人士的最低限度概念

案例/时间

 
彻底理解 CASE 的概念（及其附带的 When 语句）对于完全掌握 SQL 至关重要。 case 语句允许我们检查某些条件并根据这些条件评估为真或假来返回一个值。 结合 WHERE 和 ORDER BY 等子句，CASE 允许我们将逻辑、条件和顺序带入 SQL 查询。

CASE 语句的价值不仅限于在我们的查询中提供简单的条件逻辑。 经验丰富的数据科学家应该对 CASE 声明及其用途有更多的了解。 面试官可能会问你关于不同类型的 CASE 表达式以及如何编写它们的问题。 

有经验的候选人应该准备好回答理论问题，例如解释 Valued 和 Searched CASE 语句之间的区别、它们如何工作以及如何编写它们。 这需要对它们的语法和常见做法有深刻的理解。 不用说，这也包括正确使用 ELSE 子句。

经验丰富的数据科学家也应该知道如何将 CASE 与聚合函数一起使用。 您可能还会被要求编写一个简写的 CASE 语句，该语句重复性较低且更易于理解。 您应该能够明智地谈论使用速记 CASE 语句的注意事项和可能的风险。

一般来说，经验丰富的数据科学家必须能够使用 CASE 编写更高效的查询。 毕竟，CASE 语句的全部目的是避免编写太多单独的查询来整合数据。

这是可以使用 CASE / WHEN 语句解决的问题的示例： https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

这是 Airbnb 面试中提出的一个难题，候选人必须找到平均房东回复率、邮政编码及其相应的清洁费。

在这种情况下，除邮政编码外，还使用 ​​CASE/WHEN 语句将结果格式化为数字并将其显示为百分比值。

SQL连接

 
很容易对 SQL 连接的知识充满信心，但是你越探索这个主题，你就会发现你不知道的越多。 面试官经常问 关于 SQL 连接高级方面的面试问题 往往被忽视。 所以深入研究这个概念并彻底掌握它是很重要的。
 
除了基本概念外，面试官可能会询问什么是自我交叉连接，并通过询问解决实际问题来了解您的知识深度。 您应该了解所有不同类型的连接，包括更复杂的类型，例如散列连接或复合连接。 还可能会要求您解释什么是自然连接，以及它们何时最有用。 有时您必须解释自然连接和内部连接之间的区别。
 
一般来说，您应该对结合使用连接和其他语句来达到预期结果有全面的经验和掌握。 例如，您应该知道如何使用 WHERE 子句来使用交叉连接，就好像它是内连接一样。 您还应该知道如何使用连接来生成新表，而不会对服务器造成太大压力。 或者查询数据库时如何使用外连接来识别和填充缺失值。 或者外部连接的内部工作原理，例如重新排列它们的顺序可以改变输出。 

这是一个涉及编写内部联合声明的问题示例. 

这是一个相当困难的问题，要求候选人将订单大小显示为总支出的百分比。
 
 

高级概念 N1：日期时间操作

数据库通常包含日期和时间，因此任何有经验的数据科学家都应该对使用它们有深入的了解。 这种类型的数据使我们能够跟踪事件发生的顺序、频率的变化、计算间隔并获得其他重要的见解。 很多时候执行这些操作需要完全掌握 SQL 中的日期时间操作。 因此，具有这样一套技能的专业人士将比竞争候选人更有优势。 如果您对自己的技能没有 100% 的信心，请查看下面描述的概念，看看其中有多少听起来很熟悉。

由于在 SQL 中格式化数据有许多不同（但有效）的方法，所以优秀的编码人员至少应该熟悉它们。 在面试过程中，招聘经理希望了解基本的数据格式化概念，并能够明智地谈论为任务选择正确的功能。 这包括了解重要的 FORMAT() 函数以及充分利用该函数的相关语法。 其他基本函数的知识，例如 NOW() 也是预期的。 此外，对于有经验的专业人士来说，被问及诸如时间序列数据及其用途等基本概念也不会是突如其来的。

考虑你申请的工作的背景也很重要。 人工智能或物联网公司会更关心跟踪从传感器收集的数据，而股票交易应用程序可能需要您跟踪一天、一周或一个月的价格波动。

在某些情况下，雇主可能会询问 SQL 中更高级的日期/时间函数，例如 CAST()、EXTRACT() 或 DATE_TRUNC()。 当您处理包含日期的大量数据时，这些函数可能非常有用。 经验丰富的数据科学家应该知道每个功能的用途及其应用。 在理想情况下，他或她应该有过去使用它们的经验。

SQL 中最复杂的日期时间操作将涉及基本功能和高级功能的组合。 所以有必要了解它们，从更基本的 FORMAT()、NOW()、CURRENT_DATE 和 CURRENT_TIME 开始，包括上面提到的更高级的函数。 作为一名经验丰富的数据科学家，您还应该知道 INTERVAL 的作用以及何时使用它。

以下是 Airbnb 面试中提出的问题示例，候选人必须使用可用数据来跟踪Airbnb的增长。
 
 

前提：

 
在这个问题中，候选人被要求根据每年注册的房东数量的变化来跟踪 Airbnb 的增长。 换句话说，我们将使用新注册主机的数量作为每年增长的指标。 我们将通过计算去年和今年之间的主机数量的差异并将该数字除以上一年注册的主机数量来找到增长率。 然后我们将结果乘以 100 得到百分比值。

输出表应该有列和对应的数据，表示当年、上一年的主机数量，以及逐年增长的百分比。 百分比必须四舍五入到最接近的整数，并且行必须根据年份按升序排列。
 
 

解决方案：

 
要回答这个问题，候选人必须使用名为“airbnb_search_details”的表，该表包含许多列。 我们需要的列标记为“host_since”，表示主机首次注册网站的年、月和日。 对于这个练习，月份和日期无关紧要，所以我们需要做的第一件事是从值中提取年份。 然后，我们必须创建一个视图，其中包含当前年份、上一年和当年主机总数的单独列。 

选择 extract(year FROM host_since::DATE) FROM airbnb_search_details WHERE host_since IS NOT NULL

到目前为止，我们做了两件事：

1. 我们确保只包含 host_since 列不为空的行。
2. 我们从数据中提取了年份并将其转换为 DATE 值。

选择 extract(year FROM host_since::DATE) count(id) as current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY extract(year FROM host_since::DATE) ORDER BY Year asc

然后我们继续计算 id 并为每年设置 GROUP BY 子句。 并使其按升序显示。 

这应该给我们一个包含两列的表：年份和当年注册的主机数量。 我们仍然没有解决问题所需的全貌，但这是朝着正确方向迈出的一步。 我们还需要为上一年注册的主机提供单独的列。 这就是 LAG() 函数的用武之地。

选择 Year, current_year_host, LAG(current_year_host, 1) OVER（ORDER BYyear）作为 prev_year_host 选择 extract(year FROM host_since::DATE) count(id) 作为 current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY extract(year FROM host_since: :日期) 按年份 asc 排序

在这里，我们添加了第三列，它将被标记为“prev_year_host”，它的值将来自“current_year_host”，除了一行的延迟。 这可能是这样的：

以这种方式排列表格使得计算最终增长率非常方便。 对于方程中的每个值，我们都有一个单独的列。 最终，我们的代码应该是这样的：

SELECTyear,current_year_host,prev_year_host,round(((current_year_host - prev_year_host)/(cast(prev_year_host AS numeric)))*100)estimated_growth FROM(SELECTyear,current_year_host,LAG(current_year_host,1)OVER(ORDER BYyear)AS prev_year_host FROM (SELECT extract(year FROM host_since::date) ASyear, count(id) current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY extract(year FROM host_since::date) ORDER BYyear) t1) t2

在这里，我们添加另一个查询和另一个列来计算增长率。 我们必须将初始结果乘以 100 并四舍五入以满足任务的要求。 

这就是这个任务的解决方案。 很明显，日期时间操作函数对于完成任务至关重要。 
 
 

高级概念 N2：窗口函数和分区

SQL 窗口函数 是编写复杂但高效的 SQL 查询的最重要概念之一。 经验丰富的专业人员应该对窗函数有深入的实践和理论知识。 这包括知道什么是 over 子句并掌握它的用法。 面试官可能会问 OVER 子句如何将聚合函数转换为窗口函数。 您可能还会被问及可用作窗口函数的三个聚合函数。 经验丰富的数据科学家也应该了解其他非聚合窗口函数。

要充分利用窗口函数，还必须知道 PARTITION BY 子句是什么以及如何使用它。 可能会要求您对其进行解释并提供一些用例的示例。 有时您必须使用 ORDER_BY 子句来组织分区内的行。

能够证明对每个单独的窗口函数（例如 ROW_NUMBER()）有透彻了解的候选人将具有优势。 不用说，光有理论知识是不够的——专业人员还应该有在实践中使用它们的经验，无论有没有分区。 例如，经验丰富的专业人员应该能够解释 RANK() 和 DENSE_RANK() 之间的区别。 一个理想的候选人应该知道一些最先进的概念，例如分区内的框架，并且能够清楚地解释它们。

优秀的候选人还应该解释 NTH_VALUE() 函数的使用。 提及此函数的替代方案（例如 FIRST_VALUE() 和 LAST_VALUE() 函数）不会有什么坏处。 公司通常喜欢衡量四分位数、分位数和百分位数。 要执行此操作，数据科学家还必须知道如何使用 NTILE() 窗口函数。

在 SQL 中，通常有很多方法可以处理一项任务。 尽管如此，窗口函数还是提供了执行常见但复杂操作的最简单方法。 这种窗口函数的一个很好的例子是 LAG() 或 LEAD()，所以你也应该熟悉它们。 例如，让我们看一个来自先前解决一个困难的 Airbnb 面试问题的示例：

为了显示上一年的主机数量，我们使用了带有 OVER 语句的 LAG() 函数。 这可以通过许多其他方式完成，但窗口函数允许我们只用一行 SQL 代码获得所需的结果：

LAG(current_year_host, 1) OVER (ORDER BY year) 作为 prev_year_host

许多公司需要计算一段时间内的增长。 LAG() 函数对于完成此类任务非常宝贵。
 
 

高级概念 N3：逐月增长

许多组织使用数据分析来衡量自己的绩效。 这可能需要衡量营销活动的有效性或特定投资的投资回报率。 执行此类分析需要深入了解 SQL，例如日期、时间和窗口函数。

数据科学家还必须证明他们在格式化数据并将其显示为百分比或任何其他形式方面的技能。 一般来说，要解决必须计算月增长的实际问题，您必须使用多个技能组合的组合。 一些必需的概念将是高级的（窗口函数、日期时间操作），而另一些将是基本的（聚合函数和常见的 SQL 语句）。

让我们看一下亚马逊面试官提出的一个示例问题。

前提：

 
在这个问题中，我们必须使用购买表并计算每月收入的增长或下降。 最终结果必须以特定方式（YYYY-MM 格式）格式化，并且百分比应四舍五入到第二个最接近的小数。 

解决方案：

 
在处理这样的任务时，您需要做的第一件事就是了解表格。 您还应该确定回答问题需要使用的列。 你的输出会是什么样子。

在我们的示例中，数据值具有对象类型，因此我们必须使用 CAST() 函数将它们转换为日期类型。

从 sf_transactions 中选择 to_char(cast(created_at as date), 'YYYY-MM')

该问题还指定了日期的格式，因此我们可以使用 SQL 中的 TO_CHAR() 函数以这种格式输出日期。

要计算增长，我们还应该选择 created_at 和 SUM() 聚合函数来获取该日期的总销售额。 

SELECT to_char(cast(created_at as date), 'YYYY-MM'),created_at, sum(value) FROM sf_transactions

此时，我们不得不再次使用窗口函数。 具体来说，我们将使用 LAG() 函数访问上个月的交易量并将其显示为单独的列。 为此，我们还需要一个 OVER 子句。

SELECT to_char(cast(created_at as date), 'YYYY-MM') ASyear_month,created_at,sum(value)lag(sum(value),1)OVER(ORDER BYcreated_at::date)FROM sf_transactionsGROUP BYcreated_at

根据我们到目前为止编写的代码，我们的表格看起来像这样：

在这里，我们在 sum 列中有日期和相应的总值，在 lag 列中有最后一个日期的值。 现在我们可以将值插入公式并在单独的列中显示增长率。

我们还应该删除不必要的 created_at 列，并将 GROUP BY 和 ORDER BY 子句更改为 year_month。

SELECT to_char(cast(created_at as date), 'YYYY-MM') ASyear_month, sum(value), lag(sum(value), 1) OVER (ORDER BY to_char(cast(created_at as date)) FROM sf_transactions GROUP BY年_月

一旦我们运行代码，我们的表应该只包含我们计算所必需的列。

现在我们终于可以找到解决方案了。 这是最终代码的样子：

SELECT to_char(created_at::date, 'YYYY-MM') ASyear_month, round(((sum(value) - lag(sum(value), 1) OVER w) / (lag(sum(value), 1) OVER w)) * 100, 2) AS Revenue_diff_pct FROM sf_transactions GROUP BYyear_month WINDOW w AS ( ORDER BY to_char(created_at::date, 'YYYY-MM')) ORDER BYyear_month ASC

在此代码中，我们从上一个示例中获取两列值并计算它们之间的差异。 请注意，我们还使用窗口别名来减少代码的重复性。

然后按照算法，除以当月收入，再乘以100，得到百分比值。 最后，我们将百分比值四舍五入到小数点后两位。 我们得出了满足任务所有要求的答案。 

高级概念 N4：流失率

 
尽管它与增长相反，但流失也是一个重要的指标。 许多公司会跟踪他们的流失率，特别是如果他们的商业模式是基于订阅的。 通过这种方式，他们可以跟踪丢失订阅或帐户的数量，并预测导致丢失的原因。 经验丰富的数据科学家应该知道使用哪些函数、语句和子句来计算流失率。

订阅数据非常私密，包含私人用户信息。 对于数据科学家来说，知道如何在不暴露这些数据的情况下使用这些数据也很重要。 通常计算流失率涉及公用表表达式，这是一个相对较新的概念。 最好的数据科学家应该知道 CTE 为何有用以及何时使用它们。 在使用 CTE 不可用的旧数据库时，理想的候选人应该仍然能够完成工作。

这是一个困难任务的例子. 在 Lyft 面试的候选人会收到这项任务，以计算公司司机的流失率。

为了解决这个问题，数据科学家必须使用case/when语句、LAG()等窗口函数，以及FROM/WHERE等基础子句。 

结论

 
多年的数据科学家工作在简历上看起来确实令人印象深刻，并且会获得很多面试机会。 但是，一旦您踏入大门，您仍然需要展示知识以补充多年的经验。 即使你有丰富的经验 用 SQL 编写查询，使用类似的资源并没有什么坏处 地层划痕 刷新你的知识。

 
 
内特·罗西迪 是一名数据科学家和产品战略。 他也是教授分析学的兼职教授，并且是 地层划痕，一个帮助数据科学家准备面试的平台，回答来自顶级公司的真实面试问题。 与他联系 推特：StrataScratch or LinkedIn.

来源：https://www.kdnuggets.com/2022/01/sql-interview-questions-experienced-professionals.html