[SQL 入門與面試實戰系列] 8. SQL 面試案例講解：電商/零售的題目解析

歡迎來到 [SQL 入門與面試實戰系列] 第八章！接下來的章節我們將練習實際在面試中會遇到、真刀真槍的 SQL 考題！本章會先從最常見的電商/零售題目開始 (也是大家最熟悉的考題類型)，幫助大家了解 SQL 面試的形式與掌握解題技巧。

首先讓我們了解一下常見的 SQL 面試流程。通常 SQL 關面試時間為 15 分鐘到 1 小時，根據題目數量與難度調整，1~3 題大約是 15~30 分鐘、3 題以上的則會給到 30 分鐘~1 小時。通常面試官會提供數張資料表的 schema (說明每個欄位的名稱和含義)，或是直接提供資料表範例，題目會圍繞在這幾張表展開。

最重要的一點，大多數的 SQL 面試都是白板測驗！不像我們平時練習可以透過 Google Colab 撰寫並實際運行查詢，在面試中需要在不執行查詢的情況下撰寫語法。因此接下來我們最好練習直接在記事本或是 Word 撰寫語法，之後再來對答案。

在開始模擬考之前，讓我們再複習一下第四章節結尾提到的 SQL 執行順序，這也可以作為撰寫 SQL 時的邏輯思考步驟。

1. WITH 子句 (暫存資料表)： 定義暫存資料表，在後面撰寫的查詢可以重複使用。多數面試題不會複雜到需要用到多個暫存資料表，除非是為了讓語句排版更簡潔。
2. FROM 子句 (讀取數據源)：從指定的資料表中讀取數據，包括進行 JOIN 操作來合併資料表。在解題時可以優先思考會需要用到哪些表，並將資料表先串接好。
3. WHERE 子句 (篩選資料)： 根據題目需求對資料進行篩選。
4. GROUP BY 子句 (資料分組)：如果題目需要得到分組後的計算數據，我們在 WHERE 子句之後使用 GROUP BY 對資料進行分組。
5. HAVING 子句 (分組後的篩選)：對 GROUP BY 生成的分組進行條件篩選。這一步可以在 SELECT 子句寫完聚合函數計算數值後再進行。
6. SELECT 子句 (選取欄位與計算)： 決定返回哪些欄位，以及進行相關計算，以確定最終顯示的欄位和值。包括使用 CASE WHEN 進行條件轉換以及聚合函數計算。這步通常是撰寫一個查詢的最後一步。
7. ORDER BY子句 (結果排序)： 根據指定欄位名對查詢結果進行排序。通常在排序相關的題目可能會用到。

準備好了，就讓我們開始考試！

• 電商/零售案例 1 (難度: ★)
• 電商/零售案例 2 (難度: ★★)

• 電商/零售案例 1 (難度: ★)

橘色電商的基本考題，讓我們試著在 25 分鐘內解決。

題目：

1. 找出過去三十天訂單量小於 5 單，且年齡層介於 20–30 歲的顧客以及其單量。
2. 計算各年齡層（< 20 歲 / 20–29 歲 / 30–39 歲 / 40–49 歲 / >= 50 歲) 的不重複顧客數，以及過去三十天總 GMV。

開始計時 25 分鐘！

講解：

這題所提供的資料表是大家所熟悉的，包括訂單資料表、訂單明細資料表、以及顧客資料表。看到這幾張表，大家要本能地知道如何將這些表串接起來：訂單表與訂單明細表之間使用訂單編號 (orderid) 欄位串接，而顧客表與訂單表使用會員編號 (userid) 欄位串接。

第一小題：找出過去三十天訂單量小於 5 單，且年齡層介於 20–30 歲的顧客以及其單量。
首先我們先確認題目要的東西：符合條件的顧客以及其單量。要計算訂單量會需要用到訂單資料表 (order)，所以我們可以先將 order 表寫出來：

SELECT * 
FROM `order`

接下來我們來確認篩選條件：過去三十天的訂單、年齡層介於 20–30 歲、訂單量小於 5 單的顧客。其中，過去三十天的訂單是比較好解決的，我們對 order 表的訂單日期欄位 (create_time) 進行篩選：

SELECT * 
FROM `order`
WHERE create_time BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) 
 AND CURRENT_DATE

接著篩選符合年齡條件的顧客。為了獲得顧客資訊，我們需要串接顧客資料表 (user)，再針對年齡欄位 (age) 進行篩選：

SELECT * 
FROM `order` o
INNER JOIN `user` u ON o.userid = u.userid
WHERE o.create_time BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) 
  AND CURRENT_DATE
  AND u.age BETWEEN 20 AND 29

現在我們篩選出了年齡介於 20~29 歲顧客，在過去 30 天的所有訂單資料。最後，我們對會員編號欄位 (userid) 進行 GROUP BY，計算每位顧客的訂單量，並篩選符合訂單量小於 5 單的顧客：

SELECT u.userid, COUNT(DISTINCT o.orderid) AS order_cnt
FROM `order` o
INNER JOIN `user` u ON o.userid = u.userid
WHERE o.create_time BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) 
  AND CURRENT_DATE
  AND u.age BETWEEN 20 AND 29
GROUP BY u.userid
HAVING order_cnt < 5

以上，第一小題就完成了！

第二小題：計算各年齡層（< 20 歲 / 20–29 歲 / 30–39 歲 / 40–49 歲 / >= 50 歲) 的不重複顧客數，以及過去三十天總 GMV。
要計算 GMV 一樣會用到 order 表，且這裡的訂單條件和上一小題同樣是過去三十天的訂單資料。因此，我們首先篩選出符合條件的訂單：

SELECT *
FROM `order`
WHERE create_time BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) 
  AND CURRENT_DATE

接下來，我們需要顧客的年齡和訂單金額，以便根據年齡進行分組並計算各分組的 GMV。因此，我們需要將顧客資料表（user）與訂單明細表（order_item）連接起來：

SELECT * 
FROM `order` o
INNER JOIN `order_item` oi ON o.orderId = oi.orderId
INNER JOIN `user` u ON o.userId = u.userId
WHERE o.create_time BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) 
  AND CURRENT_DATE

最後，我們對顧客的年齡進行分類，按照題目所提供的級距使用 CASE WHEN。然後根據年齡分類進行 GROUP BY，並計算每個分組的顧客數以及其過去三十天的 GMV：

SELECT 
  CASE 
    WHEN u.age < 20 THEN '<20'
    WHEN u.age BETWEEN 20 AND 29 THEN '20-29'
    WHEN u.age BETWEEN 30 AND 39 THEN '30-39'
    WHEN u.age BETWEEN 40 AND 49 THEN '40-49'
    WHEN u.age >= 50 THEN '>=50'
  END AS age_group,
  COUNT(DISTINCT u.userid) AS user_cnt,
  SUM(oi.sales) AS GMV
FROM `order` o
INNER JOIN `order_item` oi ON o.orderId = oi.orderId
INNER JOIN `user` u ON o.userId = u.userId
WHERE o.create_time BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) 
  AND CURRENT_DATE
GROUP BY age_group

以上，第二小題就解題完成了！

• 電商/零售案例 2 (難度: ★★)

如果覺得上面那題是小蛋糕，那來試試這組出自綠色零售商的考題。這組題目會運用到此前我們學到的幾乎所有技巧，題目的條件設定也較多，需要多花一點時間思考。讓我們試著在 40 分鐘內解完。

題目：

1. 統計 2022 年 1 月份購買書籍類別的會員人數及平均年齡。
2. 找出近 365 天曾有消費紀錄，但是近 90 天內沒有消費的會員。依照會員近一年的消費金額由高到低排序，提供前 100 名的顧客 ID。
3. 統計 2022 年 11 月份，各縣市銷售量前 5 名的商品名稱、商品單價、銷售量。

開始計時 40 分鐘！

講解：

這題所提供的資料表除了大家所熟悉的訂單與顧客表，還多了一張商店表 (store)，記錄了商店編號與商店所在縣市。再觀察訂單資料表，可以發現有一欄商店編號欄位 (store_no)，可以作為串接的欄位。

第一小題：統計 2022 年 1 月份購買書籍類別的會員人數及平均年齡。
要回答題目需要判斷具體購買了什麼類別的商品，我們首先使用訂單明細表 (invoice_detail)：

SELECT *
FROM invoice_detail

此外我們還需要串接會員資料表 (member)，來獲取會員的年齡資訊：

SELECT *
FROM invoice_detail i
INNER JOIN member m ON i.account_id = m.account_id

接下來設定符合條件的銷售資料，我們從 invoice_detail 表中篩選出在 2022 年 1 月購買書籍類別的資料：

SELECT *
FROM invoice_detail i
INNER JOIN member m ON i.account_id = m.account_id
WHERE i.category_name = '書籍'
 AND strftime('%Y-%m', i.tran_datetime) = '2022-01'

現在我們有了符合條件的銷售資料。最後，計算符合條件的會員人數以及其平均年齡。值得注意的是，member 表提供的是會員的生日，因此我們需要進一步用獲取年的時間函數，計算今日與顧客生日的年份差，也就是會員的年齡：

SELECT 
 COUNT(DISTINCT m.account_id) AS `會員人數`,
 AVG(YEAR(CURRENT_DATE) - YEAR(m.birthday)) AS `平均年齡`
FROM invoice_detail i
INNER JOIN member m ON i.account_id = m.account_id
WHERE i.category_name = '書籍'
 AND strftime('%Y-%m', i.tran_datetime) = '2022-01'

以上，第一小題就完成了！

第二小題：找出近 365 天曾有消費紀錄，但是近 90 天內沒有消費的會員。依照會員近一年的消費金額由高到低排序，提供前 100 名的顧客 ID。
這題要求找出符合篩選條件並且消費金額前 100 名的會員。首先我們把 invoice 表叫出來：

SELECT *
FROM invoice

這題麻煩的是條件篩選：近 365 天曾有消費紀錄，但近 90 天內沒有消費。我們將條件拆解成兩個部分，首先我們篩選出近 365 天 ~ 近 90 天 (不包含 90 天前當日) 的銷售資料：

SELECT *
FROM invoice
WHERE tran_datetime >= DATE_SUB(CURRENT_DATE, INTERVAL 365 DAY)
  AND tran_datetime < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)

此時我們篩選出來的銷售資料，仍然會包含「近 90 天內有消費的會員銷售資料」。為了排除近 90 天內有消費的會員，我們需要使用子查詢抓取這些會員的編號 (account_id)，然後使用 NOT IN 進行排除：

SELECT *
FROM invoice
WHERE tran_datetime >= DATE_SUB(CURRENT_DATE, INTERVAL 365 DAY)
  AND tran_datetime < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
  AND account_id NOT IN (
    SELECT DISTINCT account_id FROM invoice 
    WHERE tran_datetime >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
      AND tran_datetime <= CURRENT_DATE
  )

現在我們有了只留下近 365 天 ~ 近 90 天有消費的會員銷售資料！接下來我們根據 account_id 欄位，透過 GROUP BY 分組並計算每個會員的總 GMV：

SELECT account_id, SUM(amount) AS GMV
FROM invoice
WHERE tran_datetime >= DATE_SUB(CURRENT_DATE, INTERVAL 365 DAY)
  AND tran_datetime < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
  AND account_id NOT IN (
    SELECT DISTINCT account_id FROM invoice 
    WHERE tran_datetime >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
      AND tran_datetime <= CURRENT_DATE
  )
GROUP BY account_id

最後，我們將每位顧客的 GMV 降冪排序，並呈現前 100 名：

SELECT account_id, SUM(amount) AS GMV
FROM invoice
WHERE tran_datetime >= DATE_SUB(CURRENT_DATE, INTERVAL 365 DAY)
  AND tran_datetime < DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
  AND account_id NOT IN (
    SELECT DISTINCT account_id FROM invoice 
    WHERE tran_datetime >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
      AND tran_datetime <= CURRENT_DATE
  )
GROUP BY account_id
ORDER BY GMV DESC
LIMIT 100

以上，第二小題解決！

第三小題：統計 2022 年 11 月份，各縣市銷售量前 5 名的商品名稱、商品單價、銷售量。
這題需要的資訊包含：銷售量、商品名稱、商品單價 (來自訂單明細表)，以及店家所在縣市的資訊 (來自商店表)。我們首先需要將訂單明細表與訂單表串接，得到每筆訂單來自哪間店，再串接商店表以得到完整的資訊：

SELECT *
FROM invoice_detail id
INNER JOIN invoice i ON id.invoice_number = i.invoice_number
INNER JOIN store s ON i.store_no = s.store_no

接下來，我們篩選出 2022 年 11 月份的銷售資料：

SELECT *
FROM invoice_detail id
INNER JOIN invoice i ON id.invoice_number = i.invoice_number
INNER JOIN store s ON i.store_no = s.store_no
WHERE strftime('%Y-%m', i.tran_datetime) = '2022-11'

接著我們可以來計算各縣市商品的銷售量。我們使用 GROUP BY 對縣市欄位 (county)、商品名稱欄位 (item_name)、以及商品單價欄位 (price) 分組 (商品單價欄位也是分組欄位，因為商品單價並不需要計算)：

SELECT s.county, id.item_name, id.price, 
  SUM(id.quantity) AS `銷售量`
FROM invoice_detail id
INNER JOIN invoice i ON id.invoice_number = i.invoice_number
INNER JOIN store s ON i.store_no = s.store_no
WHERE strftime('%Y-%m', i.tran_datetime) = '2022-11'
GROUP BY s.county, id.item_name, id.price

最後，我們要找出每個縣市銷量前 5 高的商品。我們透過 window function 對縣市分組，並使用 ROW_NUMBER() 對各縣市的商品銷量進行排序：

SELECT s.county, id.item_name, id.price, 
  SUM(id.quantity) AS `銷售量`,
  ROW_NUMBER() OVER (PARTITION BY s.county ORDER BY SUM(id.quantity) DESC) AS rn
FROM invoice_detail id
INNER JOIN invoice i ON id.invoice_number = i.invoice_number
INNER JOIN store s ON i.store_no = s.store_no
WHERE strftime('%Y-%m', i.tran_datetime) = '2022-11'
GROUP BY s.county, id.item_name, id.price

再篩選各縣市銷量前 5 名的商品，列出商品名稱、單價與銷售量：

SELECT county, item_name, price, `銷售量`
FROM (
  SELECT s.county, id.item_name, id.price, 
     SUM(id.quantity) AS `銷售量`,
     ROW_NUMBER() OVER (PARTITION BY s.county ORDER BY SUM(id.quantity) DESC) AS rn
  FROM invoice_detail id
  INNER JOIN invoice i ON id.invoice_number = i.invoice_number
  INNER JOIN store s ON i.store_no = s.store_no
  WHERE strftime('%Y-%m', i.tran_datetime) = '2022-11'
  GROUP BY s.county, id.item_name, id.price
) t
WHERE rn <= 5

以上，就完成了題組！大家有在 40 分鐘內解出來嗎 XD

這章節提供了兩組電商/零售的題組，從中我們可以發現幾個要點：

1. 題目多與計算銷售數字相關：因此基本上是從訂單表或訂單明細表出發，再根據題目需求串接其他表（例如會員資料表、商店表）。
2. 條件設定經常跟時間相關：例如篩選某個區間的銷售資料。因此，我們在第五章所學的時間函數要盡可能的熟悉 (記住哪個環境的時間函數語法無所謂，因為白板題主要是看我們是否知道如何解題)。
3. 排名問題：有時會碰到排名的問題，這時記得可以使用 window function 來快速解決問題！

最後說明一下，我的寫法不是絕對的標準答案，畢竟每個人思考的邏輯順序不同。這邊更多是呈現我的解題邏輯和撰寫語法的方法，各位也可以在寫完後將語法丟到 GPT，請它檢查是否能正確解出答案。

此外考試時時間有限，不用刻意追求語法的精簡或美觀 (這個等回去覆盤的時候再想就好)。就用當下最直覺的邏輯以及想到的函數，去寫出語法就好了！