SQL文（クエリ）

WITH master AS (
  SELECT
    user_id
    , gender
    , product_name
    , revenue
  FROM
    sample.sales
    LEFT JOIN sample.products
    USING
      (product_id)
    LEFT JOIN sample.customers
    USING
      (user_id)
)
SELECT
  product_name
  , SUM(rev_female) AS sum_rev_female
  , SUM(rev_female) / MAX(ttl_rev) AS percent_rev_female
FROM
  (
    SELECT
      product_name
      , IF(gender = 2, revenue, 0) AS rev_female
      , (
        SELECT
          SUM(revenue)
        FROM
          sample.sales
      ) AS ttl_rev
    FROM
      master
  )
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 3

結果テーブル

サポートページに戻る