SQL文（クエリ）

WITH master AS (
  SELECT
    user_id
    , product_id
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  GROUP BY
    user_id
    , product_id
  HAVING sum_rev >= 2000
)
SELECT
  *
FROM
  (
    SELECT
      user_id
      , product_id
      , sum_rev
      , LEAD(product_id) 
      OVER (PARTITION BY user_id ORDER BY sum_rev DESC)
      AS product_id2
      , LEAD(sum_rev)
      OVER (PARTITION BY user_id ORDER BY sum_rev DESC)
      AS sum_rev2
    FROM
      master
  )
WHERE
  sum_rev = sum_rev2
ORDER BY
  1

結果テーブル

サポートページに戻る