SQL文（クエリ）

SELECT
  year_quarter
  , SUM(revenue) AS sum_rev
FROM
  (
    SELECT
      FORMAT_DATETIME("%Y-Q%Q", date_time) AS year_quarter
      , FORMAT_DATETIME("%Y-Q%Q", FIRST_VALUE(date_time) 
      OVER (PARTITION BY user_id ORDER BY date_time))
      AS first_purchase_quarter
      , revenue
    FROM
      sample.sales
  )
WHERE
  first_purchase_quarter = "2017-Q1"
AND SUBSTR(year_quarter, -2) = "Q1"
GROUP BY
  year_quarter
ORDER BY
  1

結果テーブル

サポートページに戻る