SQL文（クエリ）

WITH master AS (
  SELECT
    FORMAT_DATETIME("%Y", date_time) AS year
    , FORMAT_DATETIME("%B", date_time) AS month
    , SUM(revenue) AS sum_rev
  FROM
    sample.sales
  WHERE
    FORMAT_DATETIME("%Y", date_time) IN("2017", "2018")
  GROUP BY
    year
    , month
)
SELECT
  month
  , MAX(sum_rev_2017) AS sum_rev_2017
  , MAX(sum_rev_2018) AS sum_rev_2018
  , ROUND(MAX(sum_rev_2018) / MAX(sum_rev_2017), 2) AS change
FROM
  (
    SELECT
      month
      , IF(year = "2017", sum_rev, 0) AS sum_rev_2017
      , IF(year = "2018", sum_rev, 0) AS sum_rev_2018
    FROM
      master
  )
GROUP BY
  month
ORDER BY
  4 DESC
LIMIT 3

結果テーブル

サポートページに戻る