SQL课程的前70％似乎很简单。其余30％的员工开始遇到困难。

从2015年到2019年，我接受了四次面试，分别担任了十几家公司的数据分析师和数据分析专家。在2017年又一次失败的采访之后-当我对复杂的SQL问题感到困惑时-我开始编写一本有关中，高复杂度SQL问题的问题书，以更好地准备面试。本指南在2019年的最后一轮采访中派上了用场。在过去的一年中，我与几个朋友共享了该指南，并且由于流行病导致额外的空闲时间，因此我完善了该指南并编写了本文档。

对于初学者，有许多很棒的SQL教程。我的最爱是Zi Chung Kao 编写的Codecademy的交互式SQL和Select Star SQL 课程。但是实际上，SQL课程的前70％相当简单，而真正的困难始于其余的30％，这在初学者指南中均未涉及。因此，在对技术公司的数据分析师和数据分析专家进行采访时，经常会问这30％的问题。

出乎意料的是，我没有找到有关中等难度此类问题的详尽资料，因此我编写了本指南。

这对面试很有用，但同时会增加您当前和将来工作的效率。我个人认为，提到的某些SQL模板对于运行报表工具和数据分析功能以识别趋势的ETL系统也很有用。

内容

SQL

№ 1.
№ 2.
№ 3. ( )
1
2
3
№ 4.
№ 5.
№ 6.

№ 1.
№ 2. ( )
1
2

№ 1.
№ 2. ( )
1
2
№ 3.

您需要了解，在与数据分析师和数据分析师进行访谈时，他们不仅提出有关SQL的问题。其他常见主题包括对过去项目的讨论，A / B测试，度量标准开发和开放式分析问题。大约三年前，Quora 在Facebook上发布了有关面试产品分析师职位的技巧。在那里，将详细讨论该主题。但是，如果提高您的SQL知识对您的面试有帮助，那么本指南非常值得。

将来，我可以将本指南中的代码移植到Select Star SQL之类的网站使编写SQL语句更容易-并实时查看代码执行的结果。作为选择，将问题添加为平台问题，以准备LeetCode采访。同时，我只想发布此文档，以便人们现在可以了解此信息。

假设以及手册的使用方法

关于SQL语言知识的假设：假定您具有SQL的实际知识。您可能经常在工作中使用它，但是想磨练自己的技能，例如自联想和窗口功能。

如何使用本手册：由于面试中经常使用写字板或虚拟笔记本电脑（无需编译代码），因此我建议您用铅笔和纸为每个问题写下解决方案，并在完成后将笔记与答案进行比较。或与将担任面试官的朋友一起解决您的问题！

在用白板或记事本采访期间，较小的语法错误并不重要。但是他们会分散面试官的注意力，因此理想情况下应尝试减少他们的人数，以便将所有注意力集中在逻辑上。
给出的答案不一定是解决每个问题的唯一方法。随意写评论以及可以添加到本指南的其他解决方案！

解决SQL面试中的复杂任务的提示

首先，所有编程面试的标准技巧...

仔细听问题的描述，向面试官重复问题的实质
制定边界案例以证明您确实了解问题（即，您将要编写的最终SQL查询中将不包括的行）
( ) , — : ,
- , ,
SQL, , . , .

这里列出的一些问题是根据旧的Periscope博客文章改编而成的（大部分作者由Sean Cook于2014年左右撰写，尽管在SiSense与Periscope合并后，他的著作似乎已从材料中删除），以及关于StackOverflow的讨论。如有必要，在每个问题的开头标记来源。

在Select Star上，SQL也是明智的选择，是本文档的补充问题。

请注意，这些问题不是我自己采访中问题的字面意思，在我工作或工作的公司中也没有使用过。

自我交往任务

No. 1.百分比每月变化

上下文：了解关键指标如何变化（例如每月活跃用户的每月受众）通常很有用。假设我们有一个logins这样的表格：

| user_id | 日期|
| --------- | ------------ |
| 1 | 2018-07-01 |
| 234 | 2018-07-02 |
| 3 | 2018-07-02 |
| 1 | 2018-07-02 |
| ... | ... |
| 234 | 2018-10-04 |

目标：找到活跃用户的每月受众人数（MAU）的每月百分比变化。

解决方案：（
此解决方案与本文档中的其他代码块一样，包含有关SQL语法元素的注释，这些注释在不同的SQL变体之间可能有所不同，以及其他说明）

WITH mau AS 
(
  SELECT 
   /* 
    *       
    *  , . .   ,    . 
    *    ,   
    *
    *  Postgres  DATE_TRUNC(),   
    *      SQL   
    * . https://www.postgresql.org/docs/9.0/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
    */ 
    DATE_TRUNC('month', date) month_timestamp,
    COUNT(DISTINCT user_id) mau
  FROM 
    logins 
  GROUP BY 
    DATE_TRUNC('month', date)
  )
 
 SELECT 
    /*
    *    SELECT      . 
    * 
    *        ,   
    *    ,   , 
    *        ..
    */ 
    a.month_timestamp previous_month, 
    a.mau previous_mau, 
    b.month_timestamp current_month, 
    b.mau current_mau, 
    ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change 
 FROM
    mau a 
 JOIN 
    /*
    *   `ON b.month_timestamp = a.month_timestamp + interval '1 month'` 
    */
    mau b ON a.month_timestamp = b.month_timestamp - interval '1 month'

No. 2.标记树结构

上下文：假设您有一个tree包含两列的表：第一列指示节点，第二列指示父节点。

任务：以这样的方式编写SQL，即我们将每个节点指定为内部，根或叶或叶，以便对于上述值我们得到以下内容：

node    label  
1       Leaf
2       Inner
3       Inner
4       Leaf
5       Root

（注意：有关树状数据结构的术语的更多信息，请参见此处。但是，不需要解决此问题！）

解决方案：
致谢： Fabian Hoffman在2020年5月2日提出了这种更通用的解决方案。感谢Fabian

WITH join_table AS
(
    SELECT 
        cur.node, 
        cur.parent, 
        COUNT(next.node) AS num_children
    FROM 
        tree cur
    LEFT JOIN 
        tree next ON (next.parent = cur.node)
    GROUP BY 
        cur.node, 
        cur.parent
)

SELECT
    node,
    CASE
        WHEN parent IS NULL THEN "Root"
        WHEN num_children = 0 THEN "Leaf"
        ELSE "Inner"
    END AS label
FROM 
    join_table

替代的解决方案，没有明确的连接：

确认：威廉Chardgin于2020年5月2日提请注意需要的条件WHERE parent IS NOT NULL，这种解决方案的回报Leaf，而不是NULL。谢谢威廉！

SELECT 
    node,
    CASE 
        WHEN parent IS NULL THEN 'Root'
        WHEN node NOT IN 
            (SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
        WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
    END AS label 
 from 
    tree

No. 3.每月的用户保留（几个部分）

致谢：此任务改编自SiSense博客文章“使用自我关联来计算保留，流出和重新激活” 。

第1部分

上下文：假设我们在表中具有网站上有关用户授权的统计信息logins：

| user_id | 日期|
| --------- | ------------ |
| 1 | 2018-07-01 |
| 234 | 2018-07-02 |
| 3 | 2018-07-02 |
| 1 | 2018-07-02 |
| ... | ... |
| 234 | 2018-10-04 |

任务：编写一个请求，以接收每月保留的用户数。在我们的例子中，此参数定义为在本月和上个月登录系统的用户数。

决定：

SELECT 
    DATE_TRUNC('month', a.date) month_timestamp, 
    COUNT(DISTINCT a.user_id) retained_users 
 FROM 
    logins a 
 JOIN 
    logins b ON a.user_id = b.user_id 
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + 
                                             interval '1 month'
 GROUP BY 
    date_trunc('month', a.date)

致谢：
汤姆·莫特尔（Tom Moertel）指出，在自动加入之前预先复制user_id可使解决方案更有效，并建议使用以下代码。谢谢汤姆！

替代解决方案：

WITH DistinctMonthlyUsers AS (
  /*
  *     ** , 
  *  
  */
    SELECT DISTINCT
      DATE_TRUNC('MONTH', a.date) AS month_timestamp,
      user_id
    FROM logins
  )

SELECT
  CurrentMonth.month_timestamp month_timestamp,
  COUNT(PriorMonth.user_id) AS retained_user_count
FROM 
    DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN 
    DistinctMonthlyUsers AS PriorMonth
  ON
    CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
    AND 
    CurrentMonth.user_id = PriorMonth.user_id

第2部分

任务：现在，我们执行上一个任务，即计算每月保留的用户数-并将其倒置。我们将写一个请求，对本月尚未返回该网站的用户进行计数。也就是说，“丢失”的用户。

决定：

SELECT 
    DATE_TRUNC('month', a.date) month_timestamp, 
    COUNT(DISTINCT b.user_id) churned_users 
FROM 
    logins a 
FULL OUTER JOIN 
    logins b ON a.user_id = b.user_id 
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + 
                                         interval '1 month'
WHERE 
    a.user_id IS NULL 
GROUP BY 
    DATE_TRUNC('month', a.date)

请注意，也可以使用LEFT或解决此问题RIGHT。

第三部分

注意：这可能比实际面试时要困难的多。认为它更像是一个难题-或者您可以跳过并继续进行下一个任务。

上下文：因此我们很好地解决了之前的两个问题。根据新任务的条款，我们现在有了丢失的用户表user_churns。如果用户在过去一个月中处于活动状态，但是在此之后未处于活动状态，则将其输入到该月的表中。看起来是这样的user_churns：

| user_id | month_date |
| --------- | ------------ |
| 1 | 2018-05-01 |
| 234 | 2018-05-01 |
| 3 | 2018-05-01 |
| 12 | 2018-05-01 |
| ... | ... |
| 234 | 2018-10-01 |

任务：现在您想要进行同类群组分析，即对过去已重新激活的活动用户总数进行分析。与这些用户创建一个表。您可以使用表格user_churns并创建同类群组logins。在Postgres中，可通过访问当前时间戳current_timestamp。

决定：

WITH user_login_data AS 
(
    SELECT 
        DATE_TRUNC('month', a.date) month_timestamp,
        a.user_id,
        /* 
        *   ,    SQL,   , 
        *      SELECT   HAVING.
        *       .  
        */ 
        MAX(b.month_date) as most_recent_churn, 
        MAX(DATE_TRUNC('month', c.date)) as most_recent_active 
     FROM 
        logins a
     JOIN 
        user_churns b 
            ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date 
     JOIN
        logins c 
            ON a.user_id = c.user_id 
            AND 
            DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
     WHERE 
        DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
     GROUP BY 
        DATE_TRUNC('month', a.date),
        a.user_id
     HAVING 
        most_recent_churn > most_recent_active

第4。增加总数

致谢：该任务改编自SiSense博客文章“ SQL中的现金流建模”。

上下文：假设我们有一个transactions这样的表格：

| 日期| 现金流量|
| ------------ | ----------- |
| 2018-01-01 | -1000 |
| 2018-01-02 | -100 |
| 2018-01-03 | 50 |
| ... | ... |

cash_flow收入减去每天的费用在哪里？

目标：编写一个请求以获取每天现金流量的总计，以这样的方式最终获得以下表格：

| 日期| 累积|
| ------------ | --------------- |
| 2018-01-01 | -1000 |
| 2018-01-02 | -1100 |
| 2018-01-03 | -1050 |
| ... | ... |

决定：

SELECT 
    a.date date, 
    SUM(b.cash_flow) as cumulative_cf 
FROM
    transactions a
JOIN b 
    transactions b ON a.date >= b.date 
GROUP BY 
    a.date 
ORDER BY 
    date ASC

使用窗口函数的替代解决方案（更有效！）：

SELECT 
    date, 
    SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf 
FROM
    transactions 
ORDER BY 
    date ASC

第5条。移动平均线

致谢：此任务改编自SiSense博客文章“ MySQL和SQL Server中的移动平均值”。

注意：移动平均值可以通过多种方式计算。在这里，我们使用先前的平均值。因此，该月第七天的指标将是前六天和他本人的平均值。

上下文：假设我们有一个signups这种形式的表：

| 日期| 招牌|
| ------------ | ---------- |
| 2018-01-01 | 10 |
| 2018-01-02 | 20 |
| 2018-01-03 | 50 |
| ... | ... |
| 2018-10-01 | 35 |

任务：编写请求以获取每日注册的7天移动平均值。

决定：

SELECT 
  a.date, 
  AVG(b.sign_ups) average_sign_ups 
FROM 
  signups a 
JOIN 
  signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY 
  a.date

No. 6.几种连接条件

致谢：此任务改编自SiSense博客文章“使用SQL分析电子邮件”。

内容：假设我们的表格emails包含从该地址发送zach@g.com并在其上接收的电子邮件：

| id | 主题| 来自| 到| 时间戳|
| ---- | ---------- | -------------- | -------------- |- ------------------ |
| 1 | 优胜美地 zach@g.com | thomas@g.com | 2018-01-02 12:45:03 |
| 2 | 大苏尔| sarah@g.com | thomas@g.com | 2018-01-02 16:30:01 |
| 3 | 优胜美地 thomas@g.com | zach@g.com | 2018-01-02 16:35:04 |
| 4 | 跑步| jill@g.com | zach@g.com | 2018-01-03 08:12:45 |
| 5 | 优胜美地 zach@g.com | thomas@g.com | 2018-01-03 14:02:01 |
| 6 | 优胜美地 thomas@g.com | zach@g.com | 2018-01-03 15:01:05 |
| .. | .. | .. | .. | .. |

任务：编写请求以获取id发送给的每个字母（）的响应时间zach@g.com。不要包含其他地址的信件。假设每个线程都有一个唯一的主题。请记住，该线程可能zach@g.com与其他收件人之间有多个往返信件。

决定：

SELECT 
    a.id, 
    MIN(b.timestamp) - a.timestamp as time_to_respond 
FROM 
    emails a 
JOIN
    emails b 
        ON 
            b.subject = a.subject 
        AND 
            a.to = b.from
        AND 
            a.from = b.to 
        AND 
            a.timestamp < b.timestamp 
 WHERE 
    a.to = 'zach@g.com' 
 GROUP BY 
    a.id

窗口功能的任务

编号1.找到最大值的标识符

上下文：假设我们有一个表格，salaries其中包含以下格式的部门和员工薪水数据：

  部门名称| empno | 薪水|     
----------- + ------- + -------- +
 发展 11 | 5200 |
 发展 7 | 4200 |
 发展 9 | 4500 |
 发展 8 | 6000 |
 发展 10 | 5200 |
 人员| 5 | 3500 |
 人员| 2 | 3900 |
 销售| 3 | 4800 |
 销售| 1 | 5000 |
 销售| 4 | 4800 |

任务：写一个要求获得empno最高薪水的请求。确保您的解决方案能够处理同等工资的案件！

决定：

WITH max_salary AS (
    SELECT 
        MAX(salary) max_salary
    FROM 
        salaries
    )
SELECT 
    s.empno
FROM 
    salaries s
JOIN 
    max_salary ms ON s.salary = ms.max_salary

使用RANK()以下替代方案：

WITH sal_rank AS 
  (SELECT 
    empno, 
    RANK() OVER(ORDER BY salary DESC) rnk
  FROM 
    salaries)
SELECT 
  empno
FROM
  sal_rank
WHERE 
  rnk = 1;

No. 2.带有窗口函数的平均值和排名（几个部分）

第1部分

上下文：假设我们有一个salaries以下格式的表：

  部门名称| empno | 薪水|     
----------- + ------- + -------- +
 发展 11 | 5200 |
 发展 7 | 4200 |
 发展 9 | 4500 |
 发展 8 | 6000 |
 发展 10 | 5200 |
 人员| 5 | 3500 |
 人员| 2 | 3900 |
 销售| 3 | 4800 |
 销售| 1 | 5000 |
 销售| 4 | 4800 |

任务：编写一个查询，该查询返回相同的表，但带有一个新列，以显示部门的平均工资。我们期望这样的表：

  部门名称| empno | 薪水| avg_salary |     
----------- + ------- + -------- + ------------ +
 发展 11 | 5200 | 5020 |
 发展 7 | 4200 | 5020 |
 发展 9 | 4500 | 5020 |
 发展 8 | 6000 | 5020 |
 发展 10 | 5200 | 5020 |
 人员| 5 | 3500 | 3700 |
 人员| 2 | 3900 | 3700 |
 销售| 3 | 4800 | 4867 |
 销售| 1 | 5000 | 4867 |
 销售| 4 | 4800 | 4867 |

决定：

SELECT 
    *, 
    /*
    * AVG() is a Postgres command, but other SQL flavors like BigQuery use 
    * AVERAGE()
    */ 
    ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
    salaries

第2部分

任务：编写一个查询，该查询根据其所在部门的薪水在时间表中添加每个雇员的位置的列，薪水最高的雇员将获得该位置的位置1。我们期望使用这种形式的表格：

  部门名称| empno | 薪水| 薪金等级|     
----------- + ------- + -------- + ------------- +
 发展 11 | 5200 | 2 |
 发展 7 | 4200 | 5 |
 发展 9 | 4500 | 4 |
 发展 8 | 6000 | 1 |
 发展 10 | 5200 | 2 |
 人员| 5 | 3500 | 2 |
 人员| 2 | 3900 | 1 |
 销售| 3 | 4800 | 2 |
 销售| 1 | 5000 | 1 |
 销售| 4 | 4800 | 2 |

决定：

SELECT 
    *, 
    RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
 FROM  
    salaries

中高难度的其他任务

编号1.直方图

上下文：假设我们有一个表格sessions，其中每一行代表一个视频流会话，长度以秒为单位：

| session_id | length_seconds |
| ------------ | ---------------- |
| 1 | 23 |
| 2 | 453 |
| 3 | 27 |
| .. | .. |

任务：编写一个查询来计算间隔为5秒的会话数，即对于上述片段，结果将如下所示：

| 桶| 数|
| --------- | ------- |
| 20-25 | 2 |
| 450-455 | 1 |

适当的行标签（“ 5-10”等）的最大分数计数

解决方案：

WITH bin_label AS 
(SELECT 
    session_id, 
    FLOOR(length_seconds/5) as bin_label 
 FROM
    sessions 
 )
 SELECT 
    CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket, 
    COUNT(DISTINCT session_id) count 
 GROUP BY 
    bin_label
 ORDER BY 
    bin_label ASC

2号。交叉连接（几个部分）

第1部分

上下文：假设我们有一个表格state_streams，其中在每行上标明了州名称和来自视频托管的流式传输的总小时数：

| 州| total_streams |
| ------- | --------------- |
| 数控| 34569 |
| SC | 33999 |
| CA | 98324 |
| MA | 19345 |
| .. | .. |

（实际上，这种类型的聚合表通常具有日期列，但在此任务中我们将其排除在外）

任务：编写查询以获取状态对，状态对之间的线程总数不超过一千。对于以上代码段，我们希望看到以下内容：

| state_a | state_b |
| --------- | --------- |
| 数控| SC |
| SC | 数控|

决定：

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a
 CROSS JOIN 
    state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state <> b.state

有关信息，也可以在不显式指定联接的情况下编写交叉联接：

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a, state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state <> b.state

第2部分

注意：这是一个奖励问题，而不是一个非常重要的SQL模板。您可以跳过它！

任务：如何从以前的解决方案中修改SQL以删除重复项？例如，在同一个表的例子中，蒸汽NC和SC只有一个时间，而不是两个。

决定：

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a, state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state > b.state

3号。高级计算

致谢：该任务是根据我在StackOverflow上提出的一个问题的讨论改编而成的（我的昵称是zthomas.nc）。

注意：这可能比实际面试时要困难的多。认为它更像是一个难题-或者您可以跳过它！

上下文：假设我们有一个这样的表table，其中user一个类的不同值可以对应于同一用户class：

| 用户| 类
| ------ | ------- |
| 1 | 一个|
| 1 | b |
| 1 | b |
| 2 | b |
| 3 | 一个|

问题：假设一个类只有两个可能的值。编写查询以计算每个类中的用户数。在这种情况下，具有两个标签a并且b必须引用class的用户b。

对于我们的样本，我们得到以下结果：

| 类 数|
| ------- | ------- |
| 一个| 1 |
| b | 2 |

决定：

WITH usr_b_sum AS 
(
    SELECT 
        user, 
        SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
    FROM 
        table
    GROUP BY 
        user
), 

usr_class_label AS 
(
    SELECT 
        user, 
        CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class 
    FROM 
        usr_b_sum
)

SELECT 
    class, 
    COUNT(DISTINCT user) count 
FROM
    usr_class_label
GROUP BY 
    class 
ORDER BY 
    class ASC

一种替代解决方案使用操作SELECT员中的说明SELECT和UNION：

SELECT 
    "a" class,
    COUNT(DISTINCT user_id) - 
        (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count 
UNION
SELECT 
    "b" class,
    (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count

最佳SQL中难度面试问题

内容

假设以及手册的使用方法

解决SQL面试中的复杂任务的提示

自我交往任务

No. 1.百分比每月变化

No. 2.标记树结构

No. 3.每月的用户保留（几个部分）

第1部分

第2部分

第三部分

第4。增加总数

第5条。移动平均线

No. 6.几种连接条件

窗口功能的任务

编号1.找到最大值的标识符

No. 2.带有窗口函数的平均值和排名（几个部分）

第1部分

第2部分

中高难度的其他任务

编号1.直方图

2号。交叉连接（几个部分）

第1部分

第2部分

3号。高级计算

More articles: