SQL文（クエリ）

WITH yup AS (
  SELECT
    user_id
    , EXTRACT(YEAR FROM date_time) AS year
    , product_id
    , SUM(revenue) AS sum_rev_yup
  FROM
    sample.sales
  GROUP BY
    year
    , user_id
    , product_id
)
, yu AS (
  SELECT
    EXTRACT(YEAR FROM date_time) AS year
    , user_id
    , SUM(revenue) AS sum_rev_yu
  FROM
    sample.sales
  GROUP BY
    year
    , user_id
)
SELECT
  bsp.year
  , bsu.user_id
  , c.name
  , bsp.best_product_by_user
  , p.product_name AS best_product_name_by_user
  , sum_rev_by_user_product
FROM
  (
    SELECT
      year
      , user_id
      , MAX(best_sold_product) AS best_product_by_user
      , MAX(sum_rev_yup) AS sum_rev_by_user_product
    FROM
      (
        SELECT
          user_id
          , year
          , FIRST_VALUE(product_id)
          OVER (PARTITION BY year, user_id ORDER BY sum_rev_yup DESC)
          AS best_sold_product
          , sum_rev_yup
        FROM
          yup
      )
    GROUP BY
      year
      , user_id
  ) AS bsp
  JOIN (
      SELECT
        *
      FROM
        (
          SELECT
            year
            , user_id
            , RANK() OVER (PARTITION BY year ORDER BY sum_rev_yu DESC)
            AS rev_rank
          FROM
            yu
        )
      WHERE
        rev_rank = 1
    ) AS bsu
  USING
    (user_id, year)
  JOIN sample.products AS p
  ON bsp.best_product_by_user = p.product_id
  JOIN sample.customers AS c
  ON bsu.user_id = c.user_id

※最初のwith句の「yup」は［year］［user_id］［product_id］ごとの［sum_rev］を格納した仮想テーブルを、2番目の「yu」は［year］［user_id］ごとの［sum_rev］を格納した仮想テーブルを表しています。

結果テーブル

サポートページに戻る