AI & Data Dreamers練功房
寶箱片場規則
QUERY GATES・資料庫關卡・高階・4 題

SQL 高階・客戶購買行為分析

首單與最近一單、回購間隔、每月新舊客、RFM 分級。

這一頁是題幹試閱;登入/註冊後每題可以在瀏覽器裡執行、對答案,第一次交對 +10 XP。

  1. 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 即可。

    作答登入後可作答
  2. 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 即可。

    作答登入後可作答
  3. 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 即可。

    作答登入後可作答
  4. 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 即可。

    作答登入後可作答