上一篇 SQL 面試題進階：CTE、Window Function 與 10 道常見練習題（上），我們用五道題目練習 CTE、累積計算與排名。你應該已經發現，進階 SQL 不只是多寫幾層 WITH，而是每一層都要知道自己正在回答哪個問題。
這篇接著分享第 6～10 題，從 30 天內回購率一路練習到 Cohort 留存。這幾題更接近實務分析，也更容易出現「Query 可以執行，但指標定義不對」的情況。
練習使用的資料表與共同假設
SQL 延續入門篇，使用 Amazon Athena engine version 3 語法。orders 包含 order_id、customer_id、order_date、order_status、total_amount；order_date 是 YYYYMMDD 整數，例如 20250831，需要先轉成 DATE。
以下假設一列就是一張訂單，order_id 唯一，customer_id 與日期沒有缺漏，日期也都是有效值；completed 代表納入分析的完成訂單。total_amount 是已確認可加總、非 NULL 的最終金額，折扣、退款與運費處理一致，且已統一幣別。這些是假設，實務上要先和面試官確認，不能只靠欄位名稱猜測。
每題都附上 clean_orders，讓你可以各自複製整段 SQL 練習。它只處理訂單狀態與日期轉換，並不會自動解決重複資料、退款或異常日期。若原始資料是商品明細，請先整理成每張訂單一列；如果歷史不完整，首購相關結果只能稱為「可觀察資料中的首購」。
第 6 題：如何計算 30 天內回購率？
首購後 30 天內有沒有再買，看起來找第二筆訂單就能回答。但昨天才首購的人，和已經觀察三個月的人，真的能一起放進分母嗎？
下手之前，你可以先問：
1. 30 天內是否包含首購當天的另一張訂單，以及第 30 天？
2. 回購率分母是首購顧客，還是所有購買顧客？
3. 資料完整到哪一天？每位顧客是否都已經有完整 30 天可以觀察？
假設資料完整到 2025 年 6 月 30 日，只納入首購後已滿 30 天的顧客；同日另一張完成訂單也算回購，第 30 天包含在內。用 order_id 區分訂單，避免把首購本身算成回購。
WITH clean_orders AS (
SELECT
order_id
, customer_id
, CAST(DATE_PARSE(CAST(order_date AS VARCHAR), '%Y%m%d') AS DATE) AS order_dt
, total_amount
FROM orders
WHERE order_status = 'completed'
),
params AS (
SELECT
DATE '2025-06-30' AS as_of_date
),
ranked AS (
SELECT
o.*
, ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_dt, order_id
) AS rn
FROM clean_orders o CROSS JOIN params p
WHERE o.order_dt <= p.as_of_date
), eligible AS (
SELECT
r.customer_id
, r.order_id AS first_order_id
, r.order_dt AS first_dt
FROM ranked r CROSS JOIN params p
WHERE r.rn = 1 AND DATE_ADD('day', 30, r.order_dt) <= p.as_of_date
), flags AS (
SELECT
f.customer_id
, MAX(CASE WHEN o.order_id IS NOT NULL THEN 1 ELSE 0 END) AS has_repeat
FROM eligible f LEFT JOIN clean_orders o
ON f.customer_id = o.customer_id
AND o.order_id <> f.first_order_id
AND o.order_dt BETWEEN f.first_dt AND DATE_ADD('day', 30, f.first_dt)
GROUP BY f.customer_id
)
SELECT
COUNT(*) AS eligible_customers
, COALESCE(SUM(has_repeat), 0) AS repeat_customers
, ROUND(100.0 * SUM(has_repeat) / NULLIF(COUNT(*), 0), 2) AS repeat_rate_pct
FROM flags;先決定誰有資格進入分母，再為每位顧客建立是否回購的旗標。一個人買五次也只算一位回購顧客。沒有符合觀察期的顧客時，比例回傳 NULL，不能把「無法計算」寫成 0%。
例如 6 月 1 日首購，到 6 月 30 日還沒有滿 30 天，就不納入分母；5 月 31 日首購則可以。若公司規定同日不算回購，把日期下限改成 o.order_dt > f.first_dt。只有日期時，也不能宣稱這是精確到小時的 720 小時回購率。
第 7 題：如何找出連續兩個月購買的顧客？
同一位顧客有兩筆訂單，不代表他連續兩個月都買過。先想一下，如果兩張都在 1 月，直接對訂單使用 LAG 會得到什麼？
下手之前，你可以先問：
1. 連續兩個月是相鄰日曆月，還是兩次購買相隔 30 天？
2. 結果要列出每一組相鄰月份，還是只要顧客名單？
3. 同一個月買很多次，是否只計算一次活躍？
假設只要找出曾在相鄰日曆月完成購買的顧客，每位顧客只出現一次。先將資料整理成每位顧客每月一列。
WITH clean_orders AS (
SELECT
order_id
, customer_id
, CAST(DATE_PARSE(CAST(order_date AS VARCHAR), '%Y%m%d') AS DATE) AS order_dt
, total_amount
FROM orders
WHERE order_status = 'completed'
),
customer_months AS (
SELECT DISTINCT
customer_id
, DATE_TRUNC('month', order_dt) AS order_month
FROM clean_orders
), compared AS (
SELECT
*
, LAG(order_month) OVER (
PARTITION BY customer_id ORDER BY order_month
) AS previous_month
FROM customer_months
)
SELECT DISTINCT
customer_id
FROM compared
WHERE order_month = DATE_ADD('month', 1, previous_month)
ORDER BY customer_id;DISTINCT 先把同月多張訂單收斂成一次月活躍，LAG 才是在比較相鄰的購買月份。最後仍然要檢查相差一個月，因為上一筆有購買的月份也可能是三個月前。
1 月 31 日與 2 月 1 日各買一次，符合這題；1 月 1 日與 1 月 30 日各買一次則不符合。若想知道是哪些月份連續購買，可將最後的欄位改成 customer_id、previous_month、order_month，並保留每一組結果。
第 8 題：如何將顧客依消費金額分成 5 組？
行銷同事想把顧客分成五組，安排不同的溝通方式。你可能想到 NTILE(5)，但他要的是每組人數差不多，還是每組營收差不多？這兩件事可不一樣。
下手之前，你可以先問：
1. 分群依照歷史消費，還是最近半年？
2. 同金額顧客可以分到不同組嗎？
3. 沒有購買的會員是否也要分組？
4. 這個分群要用於發優惠券，還是只是觀察消費分布？
假設針對 2025 年上半年有完成訂單的顧客，依總消費分成五個人數盡量接近的組；接受同額顧客被分開，並以 customer_id 固定排序。
WITH clean_orders AS (
SELECT
order_id
, customer_id
, CAST(DATE_PARSE(CAST(order_date AS VARCHAR), '%Y%m%d') AS DATE) AS order_dt
, total_amount
FROM orders
WHERE order_status = 'completed'
),
customer_spend AS (
SELECT
customer_id
, SUM(total_amount) AS total_spend
FROM clean_orders
WHERE order_dt >= DATE '2025-01-01' AND order_dt < DATE '2025-07-01'
GROUP BY customer_id
)
SELECT
customer_id
, total_spend
, NTILE(5) OVER (ORDER BY total_spend DESC, customer_id) AS spend_group
FROM customer_spend
ORDER BY spend_group, total_spend DESC, customer_id;第 1 組是消費較高的顧客，但不代表它剛好貢獻 20% 營收。NTILE 主要控制每組人數，總人數無法整除時，各組人數最多差一人；如果只有三位顧客，也不會憑空產生五個都有人的組別。
假設有 10 位顧客，每組會有 2 人，但第一組可能就貢獻一半營收。如果同額顧客收到不同優惠會造成困擾，就應先討論固定金額門檻或同分處理方式，不能只因為 NTILE 寫起來方便就直接使用。
第 9 題：如何計算新客與舊客的月營收？
如果想知道營收成長來自新客還是舊客，只把第一張訂單標成新客訂單就好了嗎？先想一下：顧客在首購當月買了三次，後面兩張要算哪一邊？
下手之前，你可以先問：
1. 新客是當月註冊，還是當月第一次完成購買？
2. 首購當月的所有訂單都算新客營收，還是只算第一張？
3. 是否有完整歷史可以辨認真正的首購月份？
假設新客定義為當月首購的顧客，首購當月所有完成訂單都歸入新客營收，之後月份才算舊客。我們展示 2025 年上半年，但先用完整歷史找首購。
WITH clean_orders AS (
SELECT
order_id
, customer_id
, CAST(DATE_PARSE(CAST(order_date AS VARCHAR), '%Y%m%d') AS DATE) AS order_dt
, total_amount
FROM orders
WHERE order_status = 'completed'
),
first_purchase AS (
SELECT
customer_id
, DATE_TRUNC('month', MIN(order_dt)) AS first_month
FROM clean_orders GROUP BY customer_id
), labeled AS (
SELECT
DATE_TRUNC('month', o.order_dt) AS order_month
, o.customer_id
, o.total_amount
, CASE WHEN DATE_TRUNC('month', o.order_dt) = f.first_month
THEN 'New' ELSE 'Existing' END AS customer_type
FROM clean_orders o JOIN first_purchase f ON o.customer_id = f.customer_id
WHERE o.order_dt >= DATE '2025-01-01' AND o.order_dt < DATE '2025-07-01'
)
SELECT
order_month
, customer_type
, COUNT(DISTINCT customer_id) AS purchasing_customers
, SUM(total_amount) AS revenue
FROM labeled
GROUP BY 1, 2
ORDER BY 1, 2;first_purchase 每位顧客只有一列，因此接回訂單時不會因為多筆首購資料而放大營收。這題的分類單位是「顧客在某個月的身分」，和上集逐張標記首購、回購訂單的邏輯不同。
例如顧客 1 月首購 100 元、1 月再買 200 元、2 月買 300 元，1 月新客營收是 300 元，2 月舊客營收是 300 元。這份結果只列有訂單的月份與類別；報表若需要零值列，還要補上月份與 New／Existing 的組合。
第 10 題：如何建立 Cohort Retention Table？
公司想知道「1 月來的新客，到了 2 月還有多少人回來？」這時候可以使用 Cohort。不過，回來是指登入、開信，還是真的又買了一次？如果只拿到訂單表，我們能回答的範圍也要先說清楚。
下手之前，你可以先問：
1. Cohort 依註冊月還是首購月分組？
2. 留存代表有完成購買，還是任何活躍行為？
3. 中間一個月沒買，下一個月回來是否仍算該月留存？
4. 哪些月份已完整觀察？尚未發生的月份應該留白，還是填 0？
假設以首購月分群，後續某月有完成訂單就算該月購買留存，不要求每月連續購買。資料完整到 2025 年 6 月 30 日，展示 2025 年上半年首購的 Cohort，只產生已完整觀察的月份。
WITH clean_orders AS (
SELECT
order_id
, customer_id
, CAST(DATE_PARSE(CAST(order_date AS VARCHAR), '%Y%m%d') AS DATE) AS order_dt
, total_amount
FROM orders
WHERE order_status = 'completed'
),
params AS (
SELECT
DATE '2025-06-01' AS last_complete_month
),
observed_orders AS (
SELECT
o.*
FROM clean_orders o CROSS JOIN params p
WHERE o.order_dt < DATE_ADD('month', 1, p.last_complete_month)
), first_purchase AS (
SELECT
customer_id
, DATE_TRUNC('month', MIN(order_dt)) AS cohort_month
FROM observed_orders GROUP BY customer_id
), cohort_sizes AS (
SELECT
cohort_month
, COUNT(*) AS cohort_size
FROM first_purchase
WHERE cohort_month >= DATE '2025-01-01'
GROUP BY cohort_month
), customer_months AS (
SELECT DISTINCT
customer_id
, DATE_TRUNC('month', order_dt) AS active_month
FROM observed_orders
), activity AS (
SELECT
f.cohort_month
, m.active_month
, COUNT(*) AS active_customers
FROM first_purchase f JOIN customer_months m ON f.customer_id = m.customer_id
GROUP BY 1, 2
), grid AS (
SELECT
s.cohort_month
, s.cohort_size
, t.active_month
FROM cohort_sizes s CROSS JOIN params p
CROSS JOIN UNNEST(SEQUENCE(s.cohort_month, p.last_complete_month, INTERVAL '1' MONTH)) AS t(active_month)
)
SELECT
g.cohort_month
, DATE_DIFF('month', g.cohort_month, g.active_month) AS month_number
, g.cohort_size
, COALESCE(a.active_customers, 0) AS active_customers
, ROUND(100.0 * COALESCE(a.active_customers, 0) / NULLIF(g.cohort_size, 0), 2) AS retention_pct
FROM grid g LEFT JOIN activity a
ON g.cohort_month = a.cohort_month AND g.active_month = a.active_month
ORDER BY 1, 2;先找首購月、計算固定的 Cohort 人數，再把訂單去重成每位顧客每月一列。grid 會產生已經觀察完的月份組合，所以當月沒有人回購時可以填 0；尚未走到的月份則沒有資料列，做成樞紐表時應留白。這份 Query 輸出的是長表，可以再用報表工具把 month_number 展開成欄位。
例如 1 月 Cohort 有 100 人，2 月 20 人購買，Month 1 就是 20%。6 月 Cohort 目前只有 Month 0，不應把 Month 1 填成 0%。因為我們定義的是各月購買留存，顧客可以隔月再回來，留存率也不一定逐月下降；它同樣不是「首購後 30 天內回購率」。
寫完 Query，再問自己結果能不能回答問題
回購率要看觀察期，連續購買要先定義月份，顧客分群要考慮用途，新舊客營收要分清楚顧客身分與訂單次序，Cohort 則要區分沒有回來和還沒觀察到。這些問題確認清楚，SQL 才有明確的計算方向。
面試時，你可以試著這樣回答：「我會先確認回購的定義和資料截止日，再找出完整歷史中的首購，讓分母只包含已滿觀察期的顧客。最後檢查同日第二張訂單與第 30 天的邊界，確保結果符合剛才約定的規則。」
看完答案後，別忘了自己動手改一個條件。例如同日不算回購、排名要保留同分，或首購當月只計第一張訂單。能說清楚哪些步驟需要改，才是真的理解這段 Query。
系列閱讀：從基礎題到進階應用
SQL 面試題入門：數據分析師必會的 10 道基礎題 (上)
SQL 面試題入門：數據分析師必會的 10 道基礎題 (下)
SQL 面試題進階：CTE、Window Function 與 10 道常見練習題（上）
SQL 面試題進階：CTE、Window Function 與 10 道常見練習題（下）(本篇)
想繼續練習更多電商 SQL 面試題嗎？
如果你已經完成上面的題目，接下來可以試著挑戰更多貼近實際工作情境的 SQL 題目。
我整理了一份《電商產業 SQL 面試實戰題庫》，收錄 24 道電商商業案例，涵蓋付款失敗率、取消與退款、實際營收、新舊客、回購率、商品表現、MoM、QoQ、優惠券成效與購物轉換漏斗。
每一道題目都會提供資料表與欄位，讓你先練習釐清商業問題、拆解查詢步驟，再對照 Athena SQL 與 MySQL 8.0 的參考答案，這裏購買：https://buymeacoffee.com/aderlabsharon/e/572913
最後也提供讀者專屬折扣碼：BLOG，結帳時輸入折扣碼即可享有8折優惠～
SQL 語法參考
日期與視窗函數可參考 Athena engine version 3 官方文件、Window functions 與 日期函數說明。不同資料庫的日期語法可能不同，練習時請先確認使用的環境。
Buy me a coffee 用行動支持我的內容創作
如果文章對你有幫助，歡迎用小額贊助支持 Sharon 持續分享數據分析實務。
