SQL文（クエリ）

WITH master17 AS (
  SELECT
    user_id
    , sum_rev17
    , NTILE(10) OVER (ORDER BY sum_rev17 DESC)
    AS rev_bucket17
  FROM
    (
      SELECT
        user_id
        , SUM(revenue) AS sum_rev17
      FROM
        sample.sales
      WHERE
        FORMAT_DATETIME("%Y", date_time) = "2017"
      GROUP BY
        user_id
    )
)
, master18 AS (
  SELECT
    user_id
    , sum_rev18
    , NTILE(10) OVER (ORDER BY sum_rev18 DESC)
    AS rev_bucket18
  FROM
    (
      SELECT
        user_id
        , SUM(revenue) AS sum_rev18
      FROM
        sample.sales
      WHERE
        FORMAT_DATETIME("%Y", date_time) = "2018"
      GROUP BY
        user_id
    )
)
SELECT
  user_id
FROM
  master17
  INNER JOIN master18
  USING
    (user_id)
WHERE
  rev_bucket17 = 1
AND rev_bucket18 = 1

結果テーブル

サポートページに戻る