SQL文（クエリ）

SELECT
  *
FROM
  (
    SELECT
      CASE
        WHEN c.gender = 1 THEN "男性"
        WHEN c.gender = 2 THEN "女性"
        ELSE "その他"
      END AS gender
      , p.product_name
      , SUM(s.revenue) AS sum_rev
      , RANK() OVER (PARTITION BY gender ORDER BY SUM(s.revenue) DESC)
      AS rank_in_gender
    FROM
      sample.sales AS s
      LEFT JOIN sample.customers AS c
      USING
        (user_id)
      LEFT JOIN sample.products AS p
      USING
        (product_id)
    GROUP BY
      c.gender
      , p.product_name
    ORDER BY
      sum_rev DESC
  )
WHERE
  rank_in_gender <= 3
ORDER BY
  1
  , 3 DESC

結果テーブル

サポートページに戻る