[データ分析のためのBigQueryクエリ]　SQLで年別優良顧客推移を算出

SELECT 
　customer_No, 
　count(customer_No) as Frequency, 
　datetime_diff("2022-12-31",max(order_datetime),month) as Recency, 
　max(order_datetime) as Recentry_buy #後に使うため購入した日付も残しておきます。 
　sum(total_price) as Monetary 
FROM `dataset.table` 
WHERE order_datetime between "2022-01-01" AND "2022-12-31" 
GROUP BY customer_No 
UNION ALL 
SELECT 
　customer_No, count(customer_No) as Frequency, 
　datetime_diff("2021-12-31",max(order_datetime),month) as Recency, 
　max(order_datetime) as Recentry_buy　#後に使うため購入した日付も残しておきます。 
　sum(total_price) as Monetary 
FROM `dataset.table` 
WHERE order_datetime between "2021-01-01" AND "2021-12-31" 
GROUP BY customer_No UNION ALL 
#年ごとのRFM分析を必要な数だけUNIOで重ねる
#実行結果を保存

#このクエリはFrequencyの指標で上位20％を探していますが、
 #他の指標も同様です。 
with frequency_table as ( 
SELECT 
 frequency, 
 count(customer_code) as number_of_customer 
FROM `dataset.table` #先ほど保存した各年RFMのUNIOしたテーブル 
GROUP BY frequency 
ORDER BY frequency ASC ) 
SELECT *, 
 #累積割合を算出 
 SUM(rate) OVER (ORDER BY frequency) AS cumulative_rate 
FROM ( 
 SELECT *, number_of_customer/[`UNION`したテーブルの総レコード数] as rate FROM frequency_table ) 
ORDER BY frequecy ASC

SELECT 
　count(customer_no) as blue_tips_customer, 
　#購入日から年を抽出 
　datetime_trunc(Recentry_buy,year) as year 
FROM `dataset.table` #先ほど保存した各年RFMのUNIOしたテーブル 
WHERE 
　frequency >=[先ほど求めた上位20%の指標値] 
AND
　Monetary>=[先ほど求めた上位20%の指標値]
AND
　recency_<=[先ほど求めた上位20%の指標値] 
GROUP BY year