开发者社区> ShowMeAI> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

看看你离世界一流大厂有多远?3道Google最新SQL面试题 ⛵

简介: 朋友刚刚拿到了 Google 数据分析师的 Offer!跟她详聊了面试的3道SQL题目,把思路和参考答案一并奉上!各位近期在面试的朋友,可以拿来自测一下~【代码与数据集亲测可运行】
+关注继续查看
5d9a42b143ac45868bf3f2813dcb99f3~tplv-k3u1fbpfcp-zoom-1.image
💡 作者:韩信子@ShowMeAI
📘 数据分析◉技能提升系列https://www.showmeai.tech/tutorials/33
📘 AI 面试题库系列https://www.showmeai.tech/tutorials/48
📘 本文地址https://www.showmeai.tech/article-detail/297
📢 声明:版权所有,转载请联系平台与作者并注明出处
📢 收藏ShowMeAI查看更多精彩内容

下面是最新的 3 道 Google SQL 面试题和参考答案。这些题目面向的 Google 职位包括:数据科学 数据分析师商业智能 工程师数据工程师商业分析师

ecaea49dcd264479ab3c47389110ba3c~tplv-k3u1fbpfcp-zoom-1.image

ShowMeAI 制作了快捷即查即用的 SQL 速查表手册,大家可以在下述位置获得:

💡 面试题 1:墨西哥和美国第三高峰

b41c1af6e90f4140991c058a8cf1c815~tplv-k3u1fbpfcp-zoom-1.image

问题: 请完成1个 SQL 来找出每个国家第三高的山名,并按 ASC 顺序对国家/地区排序。

Table: mountains
+---------------------+------+-------------+
|name                 |height|country      |
+---------------------+------+-------------+
|Denalli              |20310 |United States|
|Saint Elias          |18008 |United States|
|Foraker              |17402 |United States|
|Pico de Orizab       |18491 |Mexico       |
|Popocatépetl         |17820 |Mexico       |
|Iztaccihuatl         |17160 |Mexico       |
+---------------------+------+-------------+

参考答案:

SELECT "country",
       "name"
FROM   (SELECT "country",
               "name",
               Rank()
                 OVER (
                   partition BY "country"
                   ORDER BY "height" DESC) AS "rank"
        FROM   mountains) AS m
WHERE  "rank" = 3
ORDER  BY country ASC 

💡 面试题 2:用 latest_event 查找当前打开的页数

78959118a3624620806092cbb721b354~tplv-k3u1fbpfcp-zoom-1.image

问题: 给定下表,表中包含有关页面状态更改时间的信息。完成 SQL 查找当前使用 latest_event 的页面数。 注意,表中 page_flag 列将用于识别页面是『OFF』还是『ON』。

Table: pages_info
+-------+--------------------------------------+----------+
|page_id|event_time                            |page_flag |
+-------+--------------------------------------+----------+
|1      |current_timestamp - interval '6 hours'|ON        |
|1      |current_timestamp - interval '3 hours'|OFF       |
|1      |current_timestamp - interval '1 hours'|ON        |
|2      |current_timestamp - interval '3 hours'|ON        |
|2      |current_timestamp - interval '1 hours'|OFF       |
|3      |current_timestamp                     |ON        |
+-------+--------------------------------------+----------+

参考答案:

-- 首先,对于每个页面ID,让我们选择最新的记录(基于事件时间列)。
SELECT page_id,
       Max(event_time) AS latest_event
FROM   pages_info
GROUP  BY page_id 

-- 接着,我们将前面的查询与原表连接起来,并检查其中有多少人的标记页等于ON。
WITH latest_event
     AS (SELECT page_id,
                Max(event_time) AS latest_event
         FROM   pages_info
         GROUP  BY page_id)
SELECT Sum(CASE
             WHEN page_flag = 'ON' THEN 1
             ELSE 0
           END) AS result
FROM   pages_info pi
       JOIN latest_event le
         ON pi.page_id = le.page_id
            AND pi.event_time = le.latest_event; 

💡 面试题 3:回访用户

e66852eaa8fd4767a2449283546c0b80~tplv-k3u1fbpfcp-zoom-1.image

问题: 在如下的数据库表中,包含有关用户访问网页的信息。 完成 SQL 返回连续访问该页面最长的 3 个用户,按长短的倒序排列 3 个用户。

Table: visits
+--------+----------------------------+
|user_id |date                        | 
+--------+----------------------------+
|1       |current_timestamp::DATE - 0 |
|1       |current_timestamp::DATE - 1 |
|1       |current_timestamp::DATE - 2 |
|1       |current_timestamp::DATE - 3 |
|1       |current_timestamp::DATE - 4 |
|2       |current_timestamp::DATE - 1 |
|4       |current_timestamp::DATE - 0 |
|4       |current_timestamp::DATE - 1 |
|4       |current_timestamp::DATE - 3 |
|4       |current_timestamp::DATE - 4 |
|4       |current_timestamp::DATE - 62|   
+--------+----------------------------+

参考答案:

--首先,让我们添加一个新的列,其值是每个用户的下一次访问(与当前日期不同)。我们将使用lead函数来完成:
SELECT DISTINCT user_id,
                date,
                Lead(date)
                  OVER (
                    partition BY user_id
                    ORDER BY date) AS next_date
FROM   (SELECT DISTINCT *
        FROM   visits) AS t; 
--接着,让我们创建另一个列,其目的是让我们知道访问的停止。这包括检查下一个日期是否与当前日期+1是否不同。
WITH next_dates
     AS (SELECT DISTINCT user_id,
                         date,
                         Lead(date)
                           OVER (
                             partition BY user_id
                             ORDER BY date) AS next_date
         FROM   (SELECT DISTINCT *
                 FROM   visits) AS t) --去重
SELECT user_id,
       date,
       next_date,
       CASE
         WHEN next_date IS NULL
               OR next_date = date + 1 THEN 1
         ELSE NULL
       END AS streak
FROM   next_dates; 
--接着,我们将为每个用户创建一个分区,每个分区代表一个连续的访问。从概念上讲,我们要做的是,对于每个用户,取最近的记录(基于日期)并赋值为0,然后寻找下面的记录,如果访问没有停止就赋值为0,如果访问停止就赋值为1(如果连胜列为空),然后继续这样做,直到每个连续访问被一个不同的分区所代表。执行这一逻辑的代码如下。
WITH next_dates
     AS (SELECT DISTINCT user_id,
                         date,
                         Lead(date)
                           OVER (
                             partition BY user_id
                             ORDER BY date) AS next_date
         FROM   (SELECT DISTINCT *
                 FROM   visits)),
     streaks
     AS (SELECT user_id,
                date,
                next_date,
                CASE
                  WHEN next_date IS NULL
                        OR next_date = date + 1 THEN 1
                  ELSE NULL
                END AS streak
         FROM   next_dates)
SELECT *,
       Sum(CASE
             WHEN streak IS NULL THEN 1
             ELSE 0
           END)
         OVER (
           partition BY user_id
           ORDER BY date) AS partition
FROM   streaks; 
--一旦我们有了这个分区,问题就容易了,现在我们只需要计算每个用户和分区的记录数,并找到计数最多的用户。完整的查询如下
WITH next_dates AS
(
                SELECT DISTINCT user_id,
                                date,
                                Lead(date) OVER (partition BY user_id ORDER BY date) AS next_date
                FROM            visits ), streaks AS
(
       SELECT user_id,
              date,
              next_date,
              CASE
                     WHEN next_date IS NULL
                     OR     next_date = date + 1 THEN 1
                     ELSE NULL
              END AS streak
       FROM   next_dates ), partitions AS
(
         SELECT   *,
                  Sum(
                  CASE
                           WHEN streak IS NULL THEN 1
                           ELSE 0
                  END ) OVER (partition BY user_id ORDER BY date) AS partition
         FROM     streaks ), count_partitions AS
(
         SELECT   user_id,
                  partition,
                  Count(1) AS streak_days
         FROM     partitions
         GROUP BY user_id,
                  partition )
SELECT   user_id,
         Max(streak_days) AS longest_streak
FROM     count_partitions
GROUP BY user_id
ORDER BY 2 DESC limit 3;

参考资料

e9190f41b8de4af38c8a1a0c96f0513b~tplv-k3u1fbpfcp-zoom-1.image

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Google Earth Engine ——1979-2020年帕尔默干旱严重程度指数PDSI数据集每月三次提供高空间分辨率(约 4 公里)
Google Earth Engine ——1979-2020年帕尔默干旱严重程度指数PDSI数据集每月三次提供高空间分辨率(约 4 公里)
90 0
Google Earth Engine ——数据全解析专辑(世界第 4 版网格化人口 (GPWv4) 修订版30 弧秒1公里格网)世界无人区数据集
Google Earth Engine ——数据全解析专辑(世界第 4 版网格化人口 (GPWv4) 修订版30 弧秒1公里格网)世界无人区数据集
80 0
Google Earth Engine——基于2000-2017年时间序列的长期MODIS LST昼夜温差标准偏差,在1公里分辨率。
Google Earth Engine——基于2000-2017年时间序列的长期MODIS LST昼夜温差标准偏差,在1公里分辨率。
26 0
Google Earth Engine ——MODIS Fire_cci焚烧区像素产品5.1版数据集
Google Earth Engine ——MODIS Fire_cci焚烧区像素产品5.1版数据集
58 0
dump系列(3)Qt使用Google Breakpad捕获程序崩溃报告
dump系列(3)Qt使用Google Breakpad捕获程序崩溃报告
58 0
百度视觉团队斩获 ECCV Google AI 目标检测竞赛冠军,获奖方案全解读 | ECCV 2018
从整体方案框架来看,可分为 Fast R-CNN 和 Faster R-CNN 两种不同的训练模式。
465 0
+关注
ShowMeAI
『为AI硬核资料库(cool)而生!』构建AI解决方案,用知识加速每一次技术成长!网站:https://www.showmeai.tech/ 公众号:ShowMeAI研究中心
193
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载