SQL 高階・客戶購買行為分析
首單與最近一單、回購間隔、每月新舊客、RFM 分級。
這一頁是題幹試閱;登入/註冊後每題可以在瀏覽器裡執行、對答案,第一次交對 +10 XP。
- 01
首單與最近一單
困難視窗函式要比較回購客戶的第一張與最近一張有效訂單,看客單價是變高還是變低。有效訂單=
status不是 'cancelled';訂單金額=明細quantity * unit_price加總,沒有明細的有效訂單金額為 0。訂單的先後依
order_date;同一天有兩張時,order_id較大的算較晚。只列出至少有兩張有效訂單的客戶。輸出欄位依序為:
customer_id、第一張的order_date、第一張的金額、最近一張的order_date、最近一張的金額、金額差(最近一張減第一張)。依客戶編號由小到大排序。資料表
customers(客戶):customer_id客戶編號(文字,主鍵)、name姓名(文字)、city城市(文字,沒填為 NULL)、signup_date註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。資料表
orders(訂單):order_id訂單編號(整數,主鍵)、customer_id客戶編號(文字)、order_date下單日(文字,格式 'YYYY-MM-DD')、status狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。資料表
order_items(訂單明細,一張訂單可有多列):order_id訂單編號(整數)、product商品名稱(文字)、category類別(文字)、quantity數量(整數)、unit_price單價(整數,元)。同一張訂單裡不會出現重複的商品。本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。
作答登入後可作答 - 02
回購間隔天數
困難LAG要知道客戶多久回購一次。只看有效訂單(
status不是 'cancelled')。把每位客戶的有效訂單依order_date排序(同一天依order_id由小到大),每張訂單和它的前一張相差的天數就是一次「回購間隔」;每位客戶的第一張訂單沒有間隔。天數請用
CAST(julianday(這一張的日期) - julianday(前一張的日期) AS INTEGER)計算;同一天的兩張訂單間隔為 0。只列出至少有一次回購間隔的客戶。輸出欄位依序為:
customer_id、回購次數(間隔的個數)、最短間隔、最長間隔、平均間隔(ROUND(AVG(...), 1))。依平均間隔由短到長排序;相同時依客戶編號由小到大。資料表
customers(客戶):customer_id客戶編號(文字,主鍵)、name姓名(文字)、city城市(文字,沒填為 NULL)、signup_date註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。資料表
orders(訂單):order_id訂單編號(整數,主鍵)、customer_id客戶編號(文字)、order_date下單日(文字,格式 'YYYY-MM-DD')、status狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。資料表
order_items(訂單明細,一張訂單可有多列):order_id訂單編號(整數)、product商品名稱(文字)、category類別(文字)、quantity數量(整數)、unit_price單價(整數,元)。同一張訂單裡不會出現重複的商品。本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。
作答登入後可作答 - 03
每月新客與舊客訂單
困難客群分類要看每個月的有效訂單(
status不是 'cancelled')有多少來自新客、多少來自舊客。定義:每位客戶「第一張有效訂單」所在的月份是他的首購月。一張有效訂單如果下單月份等於該客戶的首購月,算「新客訂單」;晚於首購月,算「舊客訂單」。取消的訂單不影響首購月的判斷。月份用
strftime('%Y-%m', order_date)。每個有有效訂單的月份輸出一列,欄位依序為:月份、新客人數(這個月是首購月的不同客戶數)、新客訂單數、舊客訂單數。依月份由小到大排序。
資料表
customers(客戶):customer_id客戶編號(文字,主鍵)、name姓名(文字)、city城市(文字,沒填為 NULL)、signup_date註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。資料表
orders(訂單):order_id訂單編號(整數,主鍵)、customer_id客戶編號(文字)、order_date下單日(文字,格式 'YYYY-MM-DD')、status狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。資料表
order_items(訂單明細,一張訂單可有多列):order_id訂單編號(整數)、product商品名稱(文字)、category類別(文字)、quantity數量(整數)、unit_price單價(整數,元)。同一張訂單裡不會出現重複的商品。本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。
作答登入後可作答 - 04
客戶分級
困難RFM 分級要用 RFM 指標替有消費的客戶分級,基準日是 '2026-04-30'。只看有效訂單(
status不是 'cancelled');沒有明細的有效訂單金額為 0,但仍算一次購買。- R(最近一次距今天數)=
CAST(julianday('2026-04-30') - julianday(最近一張有效訂單的日期) AS INTEGER) - F(購買次數)=有效訂單張數
- M(消費金額)=有效訂單的明細金額(
quantity * unit_price)加總
分級規則依下列順序判斷,第一個符合的就是結果:R 大於 45 為 '流失風險';F 至少 3 而且 M 至少 6000 為 '高價值';F 至少 2 為 '回購客';其餘為 '單次客'。
只列出至少有一張有效訂單的客戶。輸出欄位依序為:
customer_id、R、F、M、等級。依客戶編號由小到大排序。資料表
customers(客戶):customer_id客戶編號(文字,主鍵)、name姓名(文字)、city城市(文字,沒填為 NULL)、signup_date註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。資料表
orders(訂單):order_id訂單編號(整數,主鍵)、customer_id客戶編號(文字)、order_date下單日(文字,格式 'YYYY-MM-DD')、status狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。資料表
order_items(訂單明細,一張訂單可有多列):order_id訂單編號(整數)、product商品名稱(文字)、category類別(文字)、quantity數量(整數)、unit_price單價(整數,元)。同一張訂單裡不會出現重複的商品。本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。
作答登入後可作答 - R(最近一次距今天數)=

送件鴿飛出去了