SQL文（クエリ）

WITH master AS (
  SELECT
    user_id
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  GROUP BY
    user_id
)
SELECT
  *
  , ROUND(accum_sum_rev_by_bucket / (
      SELECT
        SUM(sum_rev)
      FROM
        master
    ), 2) AS accum_rev_percentage
FROM
  (
    SELECT
      rev_bucket
      , sum_rev_by_bucket
      , SUM(sum_rev_by_bucket) OVER (ORDER BY rev_bucket)
      AS accum_sum_rev_by_bucket
    FROM
      (
        SELECT
          rev_bucket
          , SUM(sum_rev) AS sum_rev_by_bucket
        FROM
          (
            SELECT
              *
              , NTILE(5) OVER (ORDER BY sum_rev DESC)
              AS rev_bucket
            FROM
              master
          )
        GROUP BY
          rev_bucket
      )
  )

結果テーブル

サポートページに戻る