SQL文（クエリ）

WITH master AS (
  SELECT
    user_id
    , order_id
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  GROUP BY
    user_id
    , order_id
)
SELECT
  user_id
  , MAX(min_rev) AS min_rev
  , MAX(max_rev) AS max_rev
  , MAX(max_rev) - MAX(min_rev) AS rev_range
FROM
  (
    SELECT
      user_id
      , order_id
      , MIN(sum_rev) OVER (
        PARTITION BY
          user_id
          ORDER BY
            sum_rev ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      ) AS min_rev
      , MAX(sum_rev) OVER (
        PARTITION BY
          user_id
          ORDER BY
            sum_rev ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      ) AS max_rev
    FROM
      master
  )
GROUP BY
  user_id
HAVING COUNT(DISTINCT order_id) > 1
ORDER BY
  rev_range DESC
LIMIT 3

結果テーブル

サポートページに戻る