SQL文（クエリ）

WITH master AS (
  SELECT
    user_id
    , COUNT(DISTINCT order_id) AS orders
    , COUNT(DISTINCT product_id) AS items
    , SUM(revenue) AS total_revenue
  FROM
    sample.sales
  GROUP BY
    user_id
)
, master2 AS (
  SELECT
    user_id
    , MAX(sum_rev) AS biggest_order
  FROM
    (
      SELECT
        user_id
        , order_id
        , SUM(revenue) AS sum_rev
      FROM
        sample.sales
      GROUP BY
        user_id
        , order_id
    )
  GROUP BY
    user_id
)
SELECT
  user_id
  , orders
  , total_revenue
  , biggest_order
  , items
FROM
  (
    SELECT
      *
    FROM
      master
      JOIN master2
      USING
        (user_id)
  )
WHERE
  orders >= 7
ORDER BY
  user_id

結果テーブル

サポートページに戻る