セグメント別平均注文単価の比較

Question

顧客セグメントごとに平均注文金額を比較し、ハイタッチ施策の優先度を決めたいと依頼されました。`customers` と `orders` を結合し、支払い済み注文に限定して平均注文単価を算出するSQLを選んでください。

Accepted Answer

SELECT c.segment, AVG(o.total_amount) AS avg_order_value, COUNT(DISTINCT o.order_id) AS orders_count FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.status = 'paid' GROUP BY c.segment ORDER BY avg_order_value DESC;

Answer

SELECT c.segment, SUM(o.total_amount) / COUNT(o.order_id) AS avg_order_value FROM orders o JOIN customers c ON c.customer_id = o.customer_id GROUP BY c.segment ORDER BY avg_order_value DESC;

Answer

SELECT c.segment, AVG(o.total_amount) AS avg_order_value FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.status = 'paid' GROUP BY c.segment, o.order_id ORDER BY avg_order_value DESC;

Answer

SELECT c.segment, AVG(o.total_amount) AS avg_order_value FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.status IN ('paid', 'refunded') GROUP BY c.segment ORDER BY avg_order_value DESC;

SQL演習：マーケティング指標設計（セクション2）