FAANG数据科学面试中的五大SQL模式（附代码）

📢 转载信息

原文链接：https://www.kdnuggets.com/top-sql-patterns-from-faang-data-science-interviews-with-code

原文作者：Nate Rosidi

Image by Author

# Introduction

 
FAANG（Facebook, Amazon, Apple, Netflix, Google）等公司对数据科学职位的技术筛选非常严格。然而，即使是他们也无法无休止地提出全新的面试问题。当你经历过足够多的考察后，就会开始注意到一些 SQL 模式会反复出现。

以下是出现频率最高的 5 个模式，并附带了示例和（PostgreSQL）代码供练习。

Image by Author | Napkin AI

掌握了这些，你就能为大多数 SQL 面试做好准备了。

# Pattern #1: 使用 GROUP BY 聚合数据

 
将聚合函数与 GROUP BY 结合使用，可以按类别聚合指标。

这种模式通常与数据过滤结合使用，这意味着使用以下两个子句之一：

• WHERE：在聚合之前过滤数据。
• HAVING：在聚合之后过滤数据。

示例：这个Meta 面试问题要求你找出在 2020-02-10 之前 30 天内，每个用户的评论总数。没有评论的用户应从输出中排除。

我们使用 SUM() 函数和 GROUP BY 子句按用户对评论数量求和。通过在聚合前过滤数据，即使用 WHERE，实现了仅输出指定时间段内的评论。我们不需要计算“2020-02-10 之前的 30 天”是哪一天；我们只需使用日期函数 INTERVAL 从该日期减去 30 天即可。

SELECT user_id, SUM(number_of_comments) AS number_of_comments FROM fb_comments_count WHERE created_at BETWEEN '2020-02-10'::DATE - 30 * INTERVAL '1 day' AND '2020-02-10'::DATE GROUP BY user_id;

这是输出结果。
 

业务用途：

• 用户活跃度指标：DAU & MAU，流失率。
• 收入指标：按地区/产品/时间段划分的收入。
• 用户参与度：平均会话时长，平均每次点击次数。

# Pattern #2: 使用子查询进行过滤

 
当使用子查询进行过滤时，你首先创建一个数据子集，然后用主查询针对该子集进行过滤。

两种主要的子查询类型是：

• 标量子查询：返回单个值，例如最大金额。
• 相关子查询：引用并依赖于外部查询的结果来返回数值。

示例：这个Meta 的面试问题要求你为 Facebook 创建一个推荐系统。对于每个用户，你需要找出他们尚未关注但至少有一位朋友关注的页面。输出应包括用户 ID 以及应推荐给该用户的页面 ID。

外部查询返回所有被至少一位朋友关注的“用户-页面”对。

然后，我们在 WHERE 子句中使用子查询来排除该用户已经关注的页面。子查询中有两个条件：一个条件只考虑该特定用户关注的页面（仅检查此用户），然后检查要推荐的页面是否在用户关注的页面之列（仅检查此页面）。

由于子查询返回了用户关注的所有页面，因此在 WHERE 中使用 NOT EXISTS 排除了所有这些页面，从而实现了推荐。

SELECT DISTINCT f.user_id, p.page_id FROM users_friends f JOIN users_pages p ON f.friend_id = p.user_id WHERE NOT EXISTS (SELECT * FROM users_pages pg WHERE pg.user_id = f.user_id AND pg.page_id = p.page_id);

这是输出结果。
 

业务用途：

• 客户活动：每个用户的最近登录时间，最新的订阅变更。
• 销售：每个客户的最高订单，每个地区的最高收入订单。
• 产品表现：每个类别的最畅销产品，每个月的最高收入产品。
• 用户行为：每个用户的最长会话时间，每个客户的首单购买。
• 评论与反馈：每个产品的最佳评论者，最新评论。
• 运营：每个订单的最新发货状态，每个地区的交货速度。

# Pattern #3: 使用窗口函数进行排名

 
使用 ROW_NUMBER()、RANK() 和 DENSE_RANK() 等窗口函数，可以在数据分区内对行进行排序，然后识别第 1、第 2 或 第 n 条记录。

以下是每种排名窗口函数的作用：

• ROW_NUMBER()：在每个分区内分配一个唯一的连续编号；相同值的行会获得不同的行号。
• RANK()：为相同的值分配相同的排名，并在下一个不相同的数值出现时跳过相应的名次。
• DENSE_RANK()：与 RANK() 相同，只是在出现并列后不会跳过名次。

示例：在Amazon 面试问题中，我们需要找出 2019-02-01 和 2019-05-01 之间每天订单成本最高的那个。如果一个客户在某一天有多笔订单，则按天汇总订单成本。输出应包含客户的姓名、其订单的总成本以及订单日期。

在第一个公用表表达式 (CTE) 中，我们找出指定日期范围内的订单，并按日期汇总客户的每日总额。

在第二个 CTE 中，我们使用 RANK() 按订单成本降序为每个日期的客户排名。

现在，我们将两个 CTE 连接起来以输出所需的列，并仅过滤排名为 1 的订单，即最高订单。

WITH customer_daily_totals AS ( SELECT o.cust_id, o.order_date, SUM(o.total_order_cost) AS total_daily_cost FROM orders o WHERE o.order_date BETWEEN '2019-02-01' AND '2019-05-01' GROUP BY o.cust_id, o.order_date ), ranked_daily_totals AS ( SELECT cust_id, order_date, total_daily_cost, RANK() OVER (PARTITION BY order_date ORDER BY total_daily_cost DESC) AS rnk FROM customer_daily_totals ) SELECT c.first_name, rdt.order_date, rdt.total_daily_cost AS max_cost FROM ranked_daily_totals rdt JOIN customers c ON rdt.cust_id = c.id WHERE rdt.rnk = 1 ORDER BY rdt.order_date;

这是输出结果。
 

业务用途：

• 用户活动：“上个月最活跃的 5 个用户”。
• 收入：“收入第二高的地区”。
• 产品热度：“最畅销的 10 个产品”。
• 购买记录：“每位客户的首单购买”。

# Pattern #4: 计算移动平均值与累计求和

 
移动（滚动）平均值计算过去 N 行（通常是月或天）的平均值。它是使用 AVG() 窗口函数计算的，窗口定义为 ROWS BETWEEN N PRECEDING AND CURRENT ROW。

累积求和（运行总数）是从第一行到当前行的总和，这体现在 SUM() 窗口函数中将窗口定义为 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

示例：Amazon 的面试问题要求我们计算 3 个月的滚动平均总购买收入。我们应输出年月 (YYYY-MM) 和 3 个月的滚动平均值，按时间从最早到最近排序。

此外，退货（负购买金额）不应包括在内。

我们使用子查询通过 SUM() 计算月度收入，并使用 TO_CHAR() 函数将购买日期转换为 YYYY-MM 格式。

然后，我们使用 AVG() 计算移动平均值。在 OVER() 子句中，我们按月份对数据进行分区排序，并将窗口定义为 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW；这计算了 3 个月的移动平均值，考虑了当前月和前两个月。

SELECT t.month, AVG(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_revenue FROM (SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month, SUM(purchase_amt) AS monthly_revenue FROM amazon_purchases WHERE purchase_amt > 0 GROUP BY 1 ORDER BY 1) AS t ORDER BY t.month ASC;

这是输出结果。
 

要计算累计求和，我们这样做：

SELECT t.month, SUM(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sum FROM (SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month, SUM(purchase_amt) AS monthly_revenue FROM amazon_purchases WHERE purchase_amt > 0 GROUP BY 1 ORDER BY 1) AS t ORDER BY t.month ASC;

这是输出结果。
 

业务用途：

• 参与度指标：过去 7 天的 DAU 或消息发送量的移动平均值，累计取消订阅数。
• 财务 KPI：30 天的成本/转化/股价移动平均值，收入报告（累计年初至今）。
• 产品表现：每个用户的平均登录次数，累计应用安装量。
• 运营：累计发货订单数，累计解决的工单数，累计关闭的 Bug 数。

# Pattern #5: 应用条件聚合

 
条件聚合允许你在一次遍历中计算多个分段指标，方法是将 CASE WHEN 语句放在聚合函数内部。

示例：一个来自 Amazon 面试的问题要求你识别回访活跃用户，方法是找出用户首次购买后 1 到 7 天内进行第二次购买的用户。输出应仅包含这些用户的 ID。应忽略同一天内的购买。

第一个 CTE 识别用户及其购买日期，使用 DISTINCT 关键字排除同一天的购买。

第二个 CTE 按购买日期对每个用户的购买进行排名，从最早到最新。

最后一个 CTE 通过使用条件聚合找到每个用户的第一次和第二次购买日期。我们使用 MAX() 来选取第一个和第二个购买日期的单个非 NULL 值。

最后，我们使用最后一个 CTE 的结果，仅保留那些第二次购买日期（非 NULL）在首次购买日期之后 7 天内（包括 7 天）的用户。

WITH daily AS ( SELECT DISTINCT user_id, created_at::DATE AS purchase_date FROM amazon_transactions ), ranked AS ( SELECT user_id, purchase_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date) AS rn FROM daily ), first_two AS ( SELECT user_id, MAX(CASE WHEN rn = 1 THEN purchase_date END) AS first_date, MAX(CASE WHEN rn = 2 THEN purchase_date END) AS second_date FROM ranked WHERE rn <= 2 GROUP BY user_id ) SELECT user_id FROM first_two WHERE second_date IS NOT NULL AND (second_date - first_date) BETWEEN 1 AND 7 ORDER BY user_id;

这是输出结果。
 

业务用途：

• 订阅报告：付费用户与免费用户，按计划层级划分的活跃用户与流失用户。
• 营销漏斗仪表板：按流量来源划分的注册用户与购买用户，打开、点击与转化的邮件数量。
• 电子商务：按地区划分的完成、退款或取消的订单，新买家与回头客。
• 产品分析：按用户群组划分的 iOS 与 Android 与 Web 使用情况，使用某功能与未使用某功能的计数。
• 财务：来自新客户与现有客户的收入，总收入与净收入。
• A/B 测试与实验：控制组与处理组的指标。

# Conclusion

 
如果你想在 FAANG（以及其他公司）获得工作，请专注于这五种 SQL 模式来进行面试准备。当然，它们并非测试的全部 SQL 概念。但它们是最常被测试的。通过专注于它们，你可以确保你的面试准备工作对于大多数 FAANG 公司的 SQL 面试来说是最有效率的。
 
 

Nate Rosidi 是一位数据科学家和产品策略师。他还是教授分析学的兼职教授，并且是 StrataScratch 的创始人，该平台通过来自顶级公司的真实面试问题帮助数据科学家准备面试。Nate 撰写关于职业市场最新趋势、提供面试建议、分享数据科学项目，并涵盖所有与 SQL 相关的内容。

🚀 想要体验更好更全面的AI调用？

欢迎使用青云聚合API，约为官网价格的十分之一，支持300+全球最新模型，以及全球各种生图生视频模型，无需翻墙高速稳定，文档丰富，小白也可以简单操作。