- 金融案例 1 (難度: ★★)
這題出自黃色銀行的考題,目標在 15 分鐘內解決:
題目:
篩選出 2024 年 1 月,每位用戶的最早登入日以及其用戶是否轉帳兩次以上
Output:
開始計時 15 分鐘!
講解:
題目提供了兩張資料表:
- 登入紀錄表 (login_data)
- 轉帳紀錄表 (transfer_data)
這兩張表都是「事件」資料表,紀錄用戶的不同行為和發生時間。
了解這兩表之後,我們來看題目:篩選出 2024 年 1 月,每位用戶的最早登入日以及其用戶是否轉帳兩次以上。輸出欄位為符合條件的用戶編號、最早登入日、以及是否轉帳兩次以上。
要解決此題,我們需要將問題拆解成兩個部分:
- 從 login_data 表中篩選出 2024 年 1 月每位用戶的最早登入日期。
- 從 transfer_data 表中篩選出 2024 年 1 月每位用戶的轉帳次數。
最後將這兩部分資料合併,並判斷轉帳次數是否多於兩次。
我們先處理登入記錄的部分。首先,我們從登入紀錄表中篩選出 2024 年 1 月的紀錄:
SELECT *
FROM login_data
WHERE LoginDate >= '2024-01-01' AND LoginDate <= '2024-01-31'
接著,透過 GROUP BY 用戶編號,並搭配 MIN () 函數找出用戶在 2024 年 1 月的最早登入日:
SELECT UserId, MIN(LoginDate) AS `最早登入日`
FROM login_data
WHERE LoginDate >= '2024-01-01' AND LoginDate <= '2024-01-31'
GROUP BY UserId
登入部分的語法完成了!接下來,我們從轉帳紀錄表中篩選出 2024 年 1 月的紀錄:
SELECT *
FROM transfer_data
WHERE TransferDate >= '2024-01-01' AND TransferDate <= '2024-01-31'
然後,透過 GROUP BY 用戶編號,並搭配 COUNT() 函數計算每位用戶的轉帳次數:
SELECT UserId, COUNT(*) AS TransferCount
FROM transfer_data
WHERE TransferDate >= '2024-01-01' AND TransferDate <= '2024-01-31'
GROUP BY UserId
轉帳部分的語法也完成了。最後,我們將兩部分的結果合併,並使用 CASE WHEN 判斷轉帳次數是否多於兩次:
SELECT A.UserId, A.`最早登入日`,
CASE WHEN B.`轉帳次數` >= 2 THEN 'Y' ELSE 'N' END AS `是否轉帳兩次以上`
FROM
(SELECT UserId, MIN(LoginDate) AS `最早登入日`
FROM login_data
WHERE LoginDate >= '2024-01-01' AND LoginDate <= '2024-01-31'
GROUP BY UserId) A
LEFT JOIN
(SELECT UserId, COUNT(TransferDate) AS `轉帳次數`
FROM transfer_data
WHERE TransferDate >= '2024-01-01' AND TransferDate <= '2024-01-31'
GROUP BY UserId) B
ON A.UserId = B.UserId
以上,這題就完成囉!行有餘力的話,我們可以將子查詢放到暫存資料表讓語法更精美:
WITH user_login AS (
SELECT UserId, MIN(LoginDate) AS `最早登入日`
FROM login_data
WHERE LoginDate >= '2024-01-01' AND LoginDate <= '2024-01-31'
GROUP BY UserId
),
user_transfer AS (
SELECT UserId, COUNT(TransferDate) AS `轉帳次數`
FROM transfer_data
WHERE TransferDate >= '2024-01-01' AND TransferDate <= '2024-01-31'
GROUP BY UserId
)
SELECT A.UserId, A.`最早登入日`,
CASE WHEN B.`轉帳次數` >= 2 THEN 'Y' ELSE 'N' END AS `是否轉帳兩次以上`
FROM user_login A
LEFT JOIN user_transfer B
ON A.UserId = B.UserId
這題看起來沒有很複雜,其實需要對拆解題目有很強的直覺,尤其在時間壓力下,想到透過兩個子查詢來解題。各位順利寫出來了嗎~
- 平台案例 1 (難度: ★)
這題出自黃色叫車平台的題目,試著在 25 分鐘內解出來:
題目:
- 統計在 2024 年 4 月台北市,現金 (PAYTYPE = 1)、信用卡 (PAYTYPE = 2) 與其他支付方式的完成任務數及平均每單金額
- 統計在 2024 年 4 月每天下午 3 點至 5 點之間,台北市的任務數、完成任務數及由合作司機 (DRIVERID 開頭為 ACD) 完成的任務數
計時 25 分鐘開始!
講解:
此題組提供了三張表:
- 任務主表 (job_main):記錄任務的時間、城市與狀態。
- 任務司機表 (job_driver):記錄司機相關資訊。
- 任務支付表 (job_pay):記錄支付方式與費用。
這三張表本質上都是記錄任務的不同面向。我們可以將它們通過任務編號欄位 (JOBID) 進行串接。
先來看第一小題:統計在 2024 年 4 月台北市,現金 (PAYTYPE = 1)、信用卡 (PAYTYPE = 2) 與其他支付方式的完成任務數及平均每單金額。
首先,我們將 job_main 表和 job_pay 表,根據 JOBID 欄位進行串接:
SELECT *
FROM job_main A
INNER JOIN job_pay C ON A.JOBID = C.JOBID
接著設定題目的條件,包含:
- 時間:2024 年 4 月,我們可以使用 STRFTIME 函數,提取任務時間欄位 (JOBTIME) 的年月
- 地點:台北市
- 任務狀態:完成
SELECT *
FROM job_main A
INNER JOIN job_pay C ON A.JOBID = C.JOBID
WHERE STRFTIME('%Y-%m', A.JOBTIME) = '2024-04'
AND A.CITY = '台北市'
AND A.STATUS = 'Done'
現在有了符合條件的資料集,接著我們使用 CASE WHEN 將支付方式分類為現金 (PAYTYPE = 1)、信用卡 (PAYTYPE = 2) 和其他:
SELECT *,
CASE
WHEN C.PAYTYPE = '1' THEN '現金'
WHEN C.PAYTYPE = '2' THEN '信用卡'
ELSE '其他'
END AS `支付方式`
FROM job_main A
INNER JOIN job_pay C ON A.JOBID = C.JOBID
WHERE STRFTIME('%Y-%m', A.JOBTIME) = '2024-04'
AND A.CITY = '台北市'
AND A.STATUS = 'Done'
最後,對支付方式進行 GROUP BY 分組,並計算每種支付方式的完成任務數及平均每單金額:
SELECT
CASE
WHEN C.PAYTYPE = '1' THEN '現金'
WHEN C.PAYTYPE = '2' THEN '信用卡'
ELSE '其他'
END AS `支付方式`,
COUNT(DISTINCT A.JOBID) AS `完成任務數`,
AVG(C.FARE) AS `平均每單金額`
FROM job_main A
INNER JOIN job_pay C ON A.JOBID = C.JOBID
WHERE
STRFTIME('%Y-%m', A.JOBTIME) = '2024-04'
AND A.CITY = '台北市'
AND A.STATUS = 'Done'
GROUP BY `支付方式`
以上,第一小題完成!
接著來看第二小題:統計在 2024 年 4 月每天下午 3 點至 5 點之間,台北市的任務數、完成任務數及由合作司機 (DRIVERID 開頭為 ACD) 完成的任務數。
首先,我們將 job_main 表和 job_driver 表,根據 JOBID 欄位進行串接:
SELECT *
FROM job_main A
LEFT JOIN job_driver B ON A.JOBID = B.JOBID
接著設定題目的條件:
- 時間:2024 年 4 月的下午 3 點至 5 點,我們可以使用 STRFTIME 函數提取任務時間欄位 (JOBTIME) 的年月和小時
- 地點:台北市
SELECT *
FROM job_main A
LEFT JOIN job_driver B ON A.JOBID = B.JOBID
WHERE STRFTIME('%Y-%m', A.JOBTIME) = '2024-04'
AND STRFTIME('%H', A.JOBTIME) >= '15'
AND STRFTIME('%H', A.JOBTIME) < '17'
AND A.CITY = '台北市'
最後,我們需要統計以下三個數據:
- 任務數:使用 COUNT 計算總任務數
- 完成任務數:使用 COUNT 並搭配 CASE WHEN 過濾狀態為完成的任務
- 合作司機完成任務數:使用 COUNT 並搭配 CASE WHEN 過濾合作司機以及狀態為完成的任務 (DRIVERID 開頭為 ACD,使用 SUBSTR() 擷取字段)
SELECT
COUNT(DISTINCT A.JOBID) AS `任務數`,
COUNT(DISTINCT CASE WHEN A.STATUS = 'Done' THEN A.JOBID END) AS `完成任務數`,
COUNT(DISTINCT CASE WHEN A.STATUS = 'Done'
AND SUBSTR(B.DRIVERID, 1, 3) = 'ACD' THEN A.JOBID END) AS `合作司機完成任務數`
FROM job_main A
LEFT JOIN job_driver B ON A.JOBID = B.JOBID
WHERE STRFTIME('%Y-%m', A.JOBTIME) = '2024-04'
AND STRFTIME('%H', A.JOBTIME) >= '15'
AND STRFTIME('%H', A.JOBTIME) < '17'
AND A.CITY = '台北市'
以上,解題完成!
這個題組其實不難,主要需要熟練時間函數來擷取時間資訊,以及聚合函數搭配 CASE WHEN 來計算符合特定條件的資料。
這章節分別提供了金融和平台的題組。或許大家剛開始看到這些題目會感到有些陌生,不過練習之後應該能夠體會到以下幾點:
- 題目依然主要圍繞在事件紀錄:即使業務內容和電商零售不同,但本質上問題依然是圍繞在事件紀錄上(例如銷售紀錄 ↔ 轉帳紀錄、任務紀錄),搭配不同的條件設定,如時間、顧客、訂單狀態等。
- 資料表與常見的零售資料集不同:因此在解題時需要多花一些時間,了解題目提供的資料表記錄了什麼資訊,以及表與表之間如何進行串接。
現在,各位已經是有豐富解題經驗 (?) 的 SQL 高手了!多練習這些題目甚至自己出題,相信能夠幫助各位面試中更加游刃有餘。繼續練習,保持自信,相信你能順利通過 SQL 面試關卡!