?? 作者：韓信子@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 職位包括：資料科學 家、資料分析師、商業智能 工程師、資料工程師和商業分析師，
ShowMeAI 制作了快捷即查即用的 SQL 速查表手冊，大家可以在下述位置獲得：
- 編程語言速查表 | SQL 速查表
?? 面試題 1：墨西哥和美國第三高峰
問題： 請完成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 查找當前打開的頁數
問題： 給定下表，表中包含有關頁面狀態更改時間的資訊，完成 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：回訪用戶
問題： 在如下的資料庫表中，包含有關用戶訪問網頁的資訊， 完成 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;
參考資料
- ?? 編程語言速查表 | SQL 速查表：https://www.showmeai.tech/article-detail/99
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/502111.html
標籤：其他