SQL 面試題進階：CTE、Window Function 與 10 道常見練習題（上）

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 練習。它只處理訂單狀態與日期轉換，並不會自動解決重複資料、退款或異常日期。若原始資料是商品明細，請先整理成每張訂單一列；如果歷史不完整，首購相關結果只能稱為「可觀察資料中的首購」。

先認識 CTE 與 Window Function 在幫你做什麼

當 Query 越寫越長，你是不是也會開始忘記內層到底算了什麼？CTE 就是用 WITH 幫一段查詢結果取名字，讓後面的步驟接著使用。例如先整理訂單，再計算月營收，最後比較上月，閱讀起來就能對上自己的解題順序。CTE 的用途是整理邏輯，不代表一定會建立實體暫存表，也不保證查詢會更快。

Window Function 則適合「保留目前這一列，同時參考其他列」。例如保留訂單明細，但在旁邊加上顧客累積消費。OVER 裡的 PARTITION BY 決定分組範圍，ORDER BY 決定視窗計算順序，ROWS 可進一步指定累計範圍，最後輸出的排序，仍然要靠最外層 ORDER BY。

第 1 題：如何計算每月營收的 MoM 成長率？

看到月成長率，你是不是會想到先算每月營收，再拿這個月減掉上個月，除以上個月？方向沒有錯，但 SQL 裡的「上一列」，真的就是我們想比較的「上個月」嗎？

下手之前，你可以先問：

1. 要比較完整月份，還是本月截至今天？

2. 兩個月的觀察天數是否一致？如果不一樣，是否要改成比較 daily營收

假設資料已完整入庫，沒有訂單就代表零營收。我們比較 2025 年 1～6 月的完整月份，額外讀入 2024 年 12 月作為 1 月的比較基準。

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'
),

months AS (
    SELECT
        order_month
    FROM UNNEST(SEQUENCE(DATE '2024-12-01', DATE '2025-06-01', INTERVAL '1' MONTH)) AS t(order_month)
), 

monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_dt) AS order_month
        , SUM(total_amount) AS revenue
    FROM clean_orders
    WHERE order_dt >= DATE '2024-12-01' AND order_dt < DATE '2025-07-01'
    GROUP BY 1
), 

filled AS (
    SELECT
        m.order_month
        , COALESCE(r.revenue, 0) AS revenue
    FROM months m LEFT JOIN monthly_revenue r ON m.order_month = r.order_month
), 

compared AS (
    SELECT
        *
        , LAG(revenue) OVER (ORDER BY order_month) AS previous_revenue
    FROM filled
)

SELECT
    order_month
    , revenue
    , previous_revenue
    , ROUND(100.0 * (revenue - previous_revenue) / NULLIF(previous_revenue, 0), 2) AS mom_pct
FROM compared
WHERE order_month >= DATE '2025-01-01'
ORDER BY order_month;

先建立月份清單，再補上營收，LAG 才能對到上一個日曆月。最後才篩選展示期間，才能保留 1 月需要用到的 12 月資料。NULLIF 則讓上月營收為 0 時回傳 NULL，因為這時候一般百分比成長率沒有定義。

你可以用小例子檢查：1 月 100 元、2 月沒有訂單、3 月 150 元，3 月應該與 2 月的 0 元比較，結果是 NULL，不能跳過 2 月算成成長 50%。如果缺月是資料遺漏，也不能直接補 0。

第 2 題：如何計算每位顧客的累積消費？

如果公司想看顧客在哪一筆訂單達到 VIP 門檻，只用 GROUP BY 算出每個人的總消費，夠不夠呢？我們還需要保留每一張訂單，才能看到消費是怎麼累積上去的。

下手之前，你可以先問：

1. 要看歷史累積，還是今年重新起算？

2. 同一天有兩張訂單時，要逐筆累加，還是只看每天結束時的金額？

假設要看全部歷史的逐筆累積；目前只有日期，因此同日訂單用唯一的 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'
)

SELECT
    customer_id
    , order_dt
    , order_id
    , total_amount
    , SUM(total_amount) OVER (
        PARTITION BY customer_id ORDER BY order_dt, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spend
FROM clean_orders
ORDER BY customer_id, order_dt, order_id;

PARTITION BY 讓每位顧客各自計算，ORDER BY 決定累積順序，ROWS 則明確指定從第一列加到目前這一列。發現了嗎？Window Function 可以計算合計，同時保留訂單明細，這就是它和 GROUP BY 很實用的差別。

假設同一位顧客依序消費 500、300、200 元，累積結果就是 500、800、1,000 元。如果要判斷真實的第幾次購買達標，就要取得交易 timestamp；若只關心每日累積，應先彙總到顧客與日期。

第 3 題：如何計算前後兩次購買的間隔？

行銷同事想知道顧客大約多久會再買一次，你可能會想到用 LAG 找上一筆訂單日期。不過，兩張訂單和兩次購物行為，一定是同一件事嗎？

下手之前，你可以先問：

1. 同一天拆成兩張訂單，要算兩次購買嗎？

2. 間隔以日曆天、完整 24 小時，還是工作天計算？

3. 第一次購買沒有上一筆，應保留 NULL 還是排除？

假設一張完成訂單算一次購買，使用日曆天差；同日購買的間隔就是 0 天，第一次購買則保留 NULL。

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'
),

previous_orders AS (
    SELECT
        customer_id
        , order_id
        , order_dt
        , LAG(order_dt) OVER (PARTITION BY customer_id ORDER BY order_dt, order_id) AS previous_order_dt
    FROM clean_orders
)
SELECT
    *
    , DATE_DIFF('day', previous_order_dt, order_dt) AS interval_days
FROM previous_orders
ORDER BY customer_id, order_dt, order_id;

我們先在每位顧客內找上一張訂單，再使用 DATE_DIFF 計算天數。YYYYMMDD 整數不能直接相減當天數，所以前面的日期轉換在這裡就派上用場了。

例如 1 月 31 日買一次，2 月 1 日再買一次，間隔是 1 天。第一次購買的 NULL 不應改成 0，否則會拉低平均間隔；而只有一次購買的人沒有可計算的間隔，也不能把這個平均值解讀成所有顧客的購買週期。

第 4 題：如何找出每月消費最高的顧客？三種排名情境

「前三名」看起來只是排名，但如果第三名有兩個人同分，你會留下誰？這時候選哪個排名函數，就不只是語法偏好了。下面把同一個商業問題拆成三個子題，練習 ROW_NUMBER、RANK 與 DENSE_RANK。

下手之前，先確認：公司一定只能發出 3 份獎品，還是同分顧客都要保留？排名依照單筆最大訂單，還是每月總消費？同分時有沒有指定第二個排序條件？這些答案會直接影響最後選出哪些顧客。

以下三題都使用 orders 資料表，只計算 order_status = ‘completed’ 的訂單，並先彙總成「每月每位顧客一列」。假設 order_date 是 YYYYMMDD 格式的整數或字串，例如 20250101；月消費總額以 total_amount 加總。實務上也要先確認退款、折扣與運費是否已反映在金額中，以及訂單是否有重複資料。

4-1：每月最多選 3 位顧客發送禮品（ROW_NUMBER）

題目：公司每月最多提供 3 份禮品，要送給當月總消費最高的顧客。請找出每月獲獎名單；消費金額相同時，優先選擇 customer_id 較小的顧客。每月最多選 3 位，不足 3 位時全部保留。輸出月份、顧客 ID、月消費總額與序號。

既然禮品數量有限，就需要讓每位顧客取得唯一的序號。ROW_NUMBER 搭配 revenue DESC、customer_id ASC，可以依消費金額排序，再用顧客 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'
), monthly_spend AS (
    SELECT
        DATE_TRUNC('month', order_dt) AS order_month
        , customer_id
        , SUM(total_amount) AS revenue
    FROM clean_orders
    GROUP BY 1, 2
), ranked AS (
    SELECT
        *
        , ROW_NUMBER() OVER (
            PARTITION BY order_month
            ORDER BY revenue DESC, customer_id ASC
        ) AS rn
    FROM monthly_spend
)
SELECT
    order_month
    , customer_id
    , revenue
    , rn
FROM ranked
WHERE rn <= 3
ORDER BY order_month, rn, customer_id;

ROW_NUMBER 不會產生並列序號。即使第三位與第四位消費金額相同，也只會留下序號 3 的顧客；這就是為什麼必須先約定同額時的選擇規則。

4-2：每月競賽排名前三名，同分者都獲獎（RANK）

題目：公司依每月總消費金額舉辦排行榜，競賽排名在前三名的顧客都能獲獎。同額顧客並列，且並列會占用後續名次，例如兩人並列第二名，下一位就是第四名。請輸出月份、顧客 ID、月消費總額與競賽排名，保留排名小於或等於 3 的所有顧客。

這次不限制獲獎人數，而是依競賽名次決定資格，因此使用 RANK。視窗內只依 revenue DESC 排序，才能讓同額顧客取得相同排名。

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'
), monthly_spend AS (
    SELECT
        DATE_TRUNC('month', order_dt) AS order_month
        , customer_id
        , SUM(total_amount) AS revenue
    FROM clean_orders
    GROUP BY 1, 2
), ranked AS (
    SELECT
        *
        , RANK() OVER (
            PARTITION BY order_month
            ORDER BY revenue DESC
        ) AS spend_rank
    FROM monthly_spend
)
SELECT
    order_month
    , customer_id
    , revenue
    , spend_rank
FROM ranked
WHERE spend_rank <= 3
ORDER BY order_month, spend_rank, customer_id;

如果金額是 100、90、80、80，名次會是 1、2、3、3，四位顧客都獲獎。如果金額是 100、90、90、80，名次則是 1、2、2、4，金額 80 的顧客就不會入選。

4-3：找出每月前三個不同消費金額的所有顧客（DENSE_RANK）

題目：公司想研究高消費顧客，要找出每月最高的 3 個不同月消費總額，以及達到這些金額的所有顧客。同額顧客屬於同一級距，級距編號連續、不跳號；不足 3 個級距時全部保留。請輸出月份、顧客 ID、月消費總額與級距排名。

這裡的級距指不同的月消費總額，不是自行設定的金額區間。使用 DENSE_RANK，相同金額取得相同排名，下一個不同金額的排名接著往下編，不會因並列人數而跳號。

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'
), monthly_spend AS (
    SELECT
        DATE_TRUNC('month', order_dt) AS order_month
        , customer_id
        , SUM(total_amount) AS revenue
    FROM clean_orders
    GROUP BY 1, 2
), ranked AS (
    SELECT
        *
        , DENSE_RANK() OVER (
            PARTITION BY order_month
            ORDER BY revenue DESC
        ) AS spend_rank
    FROM monthly_spend
)
SELECT
    order_month
    , customer_id
    , revenue
    , spend_rank
FROM ranked
WHERE spend_rank <= 3
ORDER BY order_month, spend_rank, customer_id;

如果金額是 100、90、90、80，級距排名會是 1、2、2、3。因此 DENSE_RANK 小於或等於 3 會保留四位顧客，代表選出前三種金額的所有顧客。

放在一起比較：前三位、競賽前三名、前三種金額

用同一月份的五位顧客來驗證。假設 customer_id 依序為 A、B、C、D、E，月消費總額分別是 100、90、90、80、70：

顧客	月消費總額	ROW_NUMBER	RANK	DENSE_RANK
A	100	1	1	1
B	90	2	2	2
C	90	3	2	2
D	80	4	4	3
E	70	5	5	4

篩選排名小於或等於 3 時，ROW_NUMBER 與 RANK 在這組資料都選出 A、B、C；DENSE_RANK 則選出 A、B、C、D。前兩者這次剛好選到相同的人，不代表規則相同；把金額換成 100、90、80、80、70，就會看到 ROW_NUMBER 選出 3 人，RANK 選出 4 人。

三題都先在 ranked CTE 算好排名，再到外層 WHERE 篩選。使用 RANK 或 DENSE_RANK 保留同分者時，不要把 customer_id 加進視窗內的 ORDER BY，否則同額顧客會被拆成不同名次。最外層的 ORDER BY 可以加入 customer_id，因為它只決定結果的顯示順序，不會改變已算好的排名。

第 5 題：如何區分首購與回購訂單？

看到首購，你可能會想：把每個人的訂單依時間排序，第一張就是首購。可是，如果手上的資料只剩最近一年，這張「第一張」真的就是顧客人生中的第一筆訂單嗎？

下手之前，你可以先問：

1. 首購指第一張完成訂單，還是第一次付款？退款後是否重新認定？

2. 資料是否涵蓋完整歷史？是否有舊系統或其他通路的訂單？

3. 同日多張訂單要只標一張首購，還是整個首購日都算？

假設有完整歷史，一張完成訂單就是一次購買；只標記一張首購，同日以 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'
),
ranked_orders AS (
    SELECT
        *
        , ROW_NUMBER() OVER (
            PARTITION BY customer_id ORDER BY order_dt, order_id
        ) AS order_sequence
    FROM clean_orders
)
SELECT
    order_id
    , customer_id
    , order_dt
    , CASE WHEN order_sequence = 1 THEN 'First Purchase'
    ELSE 'Repeat Purchase' END AS purchase_type
FROM ranked_orders
ORDER BY customer_id, order_dt, order_id;

如果只想看 2025 年的首購與回購訂單，應該先在完整歷史上排名，再在最外層加上日期條件。如果先把舊訂單切掉，老顧客今年的第一張訂單，就會被誤判成首購。

假設顧客在 2024 年已經買過，2025 年又買一次，2025 年那張仍然是回購。只有日期時，我們採用的是明確約定的同日規則；如果業務要精確找出第一筆付款，還是需要付款時間。

練習時，試著把你的判斷說出來

這五題分別用了 LAG、SUM OVER 與排名函數，但真正會改變答案的，是缺月怎麼處理、累積從哪裡開始、同分要不要保留，以及首購歷史是否完整。你可以先遮住答案，練習把需要確認的問題說出來，再一步步寫 Query。

下一篇接著練習第 6～10 題，把這些方法放進 30 天回購率、連續購買、顧客分群、新舊客營收與 Cohort 留存分析。

系列閱讀：從基礎題到進階應用

SQL 面試題入門：數據分析師必會的 10 道基礎題 (上)

SQL 面試題入門：數據分析師必會的 10 道基礎題 (下)

SQL 面試題進階：CTE、Window Function 與 10 道常見練習題（上）(本篇)

SQL 面試題進階：CTE、Window Function 與 10 道常見練習題（下）

SQL 語法參考

日期與視窗函數可參考 Athena engine version 3 官方文件、Window functions 與日期函數說明。不同資料庫的日期語法可能不同，練習時請先確認使用的環境。

想繼續練習更多電商 SQL 面試題嗎？

如果你已經完成上面的題目，接下來可以試著挑戰更多貼近實際工作情境的 SQL 題目。

我整理了一份《電商產業 SQL 面試實戰題庫》，收錄 24 道電商商業案例，涵蓋付款失敗率、取消與退款、實際營收、新舊客、回購率、商品表現、MoM、QoQ、優惠券成效與購物轉換漏斗。

每一道題目都會提供資料表與欄位，讓你先練習釐清商業問題、拆解查詢步驟，再對照 Athena SQL 與 MySQL 8.0 的參考答案，這裏購買：https://buymeacoffee.com/aderlabsharon/e/572913

最後也提供讀者專屬折扣碼：BLOG，結帳時輸入折扣碼即可享有8折優惠～

Buy me a coffee　用行動支持我的內容創作

如果文章對你有幫助，歡迎用小額贊助支持 Sharon 持續分享數據分析實務。

以小額贊助支持 Sharon

Tagged SQL, SQL面試題, 數據分析師面試

Sharon Lab∣雪倫實驗室

數據分析師的工作日常與職場點滴

SQL 面試題進階：CTE、Window Function 與 10 道常見練習題（上）

練習使用的資料表與共同假設

先認識 CTE 與 Window Function 在幫你做什麼

第 1 題：如何計算每月營收的 MoM 成長率？

第 2 題：如何計算每位顧客的累積消費？

第 3 題：如何計算前後兩次購買的間隔？

第 4 題：如何找出每月消費最高的顧客？三種排名情境

4-1：每月最多選 3 位顧客發送禮品（ROW_NUMBER）

4-2：每月競賽排名前三名，同分者都獲獎（RANK）

4-3：找出每月前三個不同消費金額的所有顧客（DENSE_RANK）

放在一起比較：前三位、競賽前三名、前三種金額

第 5 題：如何區分首購與回購訂單？

練習時，試著把你的判斷說出來

SQL 語法參考

想繼續練習更多電商 SQL 面試題嗎？

發佈留言

Sharon Lab∣雪倫實驗室

數據分析師的工作日常與職場點滴

練習使用的資料表與共同假設

先認識 CTE 與 Window Function 在幫你做什麼

第 1 題：如何計算每月營收的 MoM 成長率？

第 2 題：如何計算每位顧客的累積消費？

第 3 題：如何計算前後兩次購買的間隔？

第 4 題：如何找出每月消費最高的顧客？三種排名情境

4-1：每月最多選 3 位顧客發送禮品（ROW_NUMBER）

4-2：每月競賽排名前三名，同分者都獲獎（RANK）

4-3：找出每月前三個不同消費金額的所有顧客（DENSE_RANK）

放在一起比較：前三位、競賽前三名、前三種金額

第 5 題：如何區分首購與回購訂單？

練習時，試著把你的判斷說出來

SQL 語法參考

想繼續練習更多電商 SQL 面試題嗎？

相關文章

SQL 面試題進階：CTE、Window Function 與 10 道常見練習題（下）

SQL 面試題入門：數據分析師必會的 10 道基礎題 (下)

SQL 面試題入門：數據分析師必會的 10 道基礎題 (上)

發佈留言