SQL文（クエリ）

WITH master AS (
  SELECT
    product_id
    , SUM(quantity) AS sum_qty
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  GROUP BY
    product_id
)
SELECT
  *
  , unit_cost * sum_qty AS sum_cost
  , (avg_rev - unit_cost) * sum_qty AS sum_profit
  , ((avg_rev - unit_cost) * sum_qty) / sum_rev AS avg_profit_rate
FROM
  (
    SELECT
      product_name
      , sum_rev / sum_qty AS avg_rev
      , cost AS unit_cost
      , sum_qty
      , sum_rev
    FROM
      master
      JOIN sample.products
      USING
        (product_id)
  )
ORDER BY
  8 DESC
LIMIT 3

結果テーブル

サポートページに戻る