Tweetを適当にためたBigQueryのTableから、恵方巻関連のつぶやきを拾ったもの。

count_ehomaki.sql

SELECT 
  year, month, day,
  count(text) AS count
FROM 
  (
    SELECT 
      YEAR(DATE_ADD (createdAt, 9, "HOUR")) AS year,
      MONTH(DATE_ADD (createdAt, 9, "HOUR")) AS month,
      DAY(DATE_ADD (createdAt, 9, "HOUR")) AS day,
      text
    FROM [gcpdemo.Tweet]
    WHERE text CONTAINS "恵方巻"
  )
GROUP BY
  year, month, day
ORDER BY
  year, month, day
LIMIT 100