解答

147

SQL文(クエリ)

WITH master AS (
  SELECT
    user_id
    , order_id
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  GROUP BY
    user_id
    , order_id
)
SELECT
  CASE gender
    WHEN 1 THEN "男性"
    WHEN 2 THEN "女性"
  END AS gender_cat
  , SUM(sum_rev) / COUNT(DISTINCT order_id) AS avg_order_value
FROM
  master
  LEFT JOIN sample.customers
  USING
    (user_id)
GROUP BY
  gender_cat

結果テーブル

結果テーブル

サポートページに戻る