SQL文（クエリ）

WITH pref17 AS (
  SELECT
    prefecture
  FROM
    sample.sales
    JOIN sample.customers
    USING
      (user_id)
  WHERE
    EXTRACT(YEAR FROM date_time) = 2017
  GROUP BY
    prefecture
  HAVING SUM(revenue) / COUNT(DISTINCT order_id) <= 2000
)
, pref18 AS (
  SELECT
    prefecture
  FROM
    sample.sales
    JOIN sample.customers
    USING
      (user_id)
  WHERE
    EXTRACT(YEAR FROM date_time) = 2018
  GROUP BY
    prefecture
  HAVING SUM(revenue) / COUNT(DISTINCT order_id) >= 6000
)
, pref19 AS (
  SELECT
    prefecture
  FROM
    sample.sales
    JOIN sample.customers
    USING
      (user_id)
  WHERE
    EXTRACT(YEAR FROM date_time) = 2019
  GROUP BY
    prefecture
  HAVING SUM(revenue) / COUNT(DISTINCT order_id) >= 6000
)
SELECT
  *
FROM
  pref17
INTERSECT DISTINCT
SELECT
  *
FROM
  pref18
INTERSECT DISTINCT
SELECT
  *
FROM
  pref19

結果テーブル

サポートページに戻る