チャーンリスク顧客の直近購買状況

Question

経営企画チームは、`lifecycle_stage = 'churn_risk'` の顧客について直近30日で支払い済み注文があるかを確認したいと考えています。アカウント名とともに購買有無を判定するSQLを選んでください。

Accepted Answer

SELECT c.full_name, a.account_name, CASE WHEN EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'paid' AND o.order_date >= CURRENT_DATE - INTERVAL '30 day' ) THEN '直近購入あり' ELSE '直近購入なし' END AS recent_purchase_status FROM customers c JOIN accounts a ON a.account_id = c.account_id WHERE c.lifecycle_stage = 'churn_risk' ORDER BY c.full_name;

Answer

SELECT c.full_name, a.account_name, CASE WHEN SUM(o.total_amount) > 0 THEN '直近購入あり' ELSE '直近購入なし' END AS recent_purchase_status FROM customers c JOIN accounts a ON a.account_id = c.account_id LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE c.lifecycle_stage = 'churn_risk' AND o.status = 'paid' AND o.order_date >= CURRENT_DATE - INTERVAL '30 day' GROUP BY c.full_name, a.account_name ORDER BY c.full_name;

Answer

SELECT DISTINCT c.full_name, a.account_name, '直近購入なし' AS recent_purchase_status FROM customers c JOIN accounts a ON a.account_id = c.account_id WHERE c.lifecycle_stage = 'churn_risk' AND NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'paid' ) ORDER BY c.full_name;

Answer

SELECT c.full_name, a.account_name, CASE WHEN EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status IN ('paid','pending') AND o.order_date >= CURRENT_DATE - INTERVAL '30 day' ) THEN '直近購入あり' ELSE '直近購入なし' END AS recent_purchase_status FROM customers c JOIN accounts a ON a.account_id = c.account_id WHERE c.lifecycle_stage = 'churn_risk' ORDER BY c.full_name;

SQL演習：顧客360°ビュー構築（セクション3）