SQL文（クエリ）

WITH master AS (
  SELECT
    EXTRACT(YEAR FROM date_time
    ) AS year
    , product_id
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  GROUP BY
    year
    , product_id
)
SELECT
  *
FROM
  (
    SELECT
      year
      , product_id
      , RANK() OVER (PARTITION BY year ORDER BY sum_rev DESC) 
      AS rev_rank
    FROM
      master
    WHERE
      year IN (2018, 2019)
  )
WHERE
  rev_rank <= 3
ORDER BY
  year
  , rev_rank

結果テーブル

サポートページに戻る