SQL文（クエリ）

WITH master AS (
  SELECT
    product_id
    , SUM(revenue) / SUM(quantity) AS proper_unit_price
  FROM
    sample.sales
  WHERE
    is_proper IS TRUE
  GROUP BY
    product_id
)
SELECT
  SUM(discount) AS sum_discount
FROM
  (
    SELECT
      quantity * (proper_unit_price - non_proper_unit_price) AS discount
    FROM
      (
        SELECT
          quantity
          , revenue / quantity AS non_proper_unit_price
          , proper_unit_price
        FROM
          sample.sales
          LEFT JOIN master
          USING
            (product_id)
        WHERE
          is_proper IS NOT TRUE
      )
  )

結果テーブル

サポートページに戻る