SQL文（クエリ）

WITH master AS (
  SELECT
    user_id
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  GROUP BY
    user_id
)
SELECT
  ROUND(COUNT(*) / (
      SELECT
        COUNT(DISTINCT user_id)
      FROM
        master
    ), 3) AS user_accum_percentage
  , ROUND(MAX(accum_sum_rev) / (
      SELECT
        SUM(sum_rev)
      FROM
        master
    ), 3) AS rev_accum_percentage
FROM
  (
    SELECT
      user_id
      , SUM(sum_rev) OVER (ORDER BY sum_rev DESC)
      AS accum_sum_rev
    FROM
      master
    ORDER BY
      sum_rev DESC
    LIMIT 100
  )

結果テーブル

サポートページに戻る