BigQueryML.sql

#standardSQL
CREATE MODEL skooldio.popularity
OPTIONS(model_type='logistic_reg') AS
SELECT 
  IF(reactions > 50, 1, 0) AS label,
  type,
  promotion_status,
  created_time,
  CAST(
    EXTRACT(DAYOFWEEK FROM 
      DATETIME(TIMESTAMP_SECONDS(created_time), 'Asia/Bangkok')
    ) AS STRING
  ) AS day, 
  IF(
    EXTRACT(HOUR FROM 
      DATETIME(TIMESTAMP_SECONDS(created_time), 'Asia/Bangkok')
    ) < 12,
    'AM', 'PM'
  ) AS time,
  IF(link LIKE '%medium.com%', 1, 0) AS is_medium_link,
  IF(link LIKE '%hbr.org%', 1, 0) AS is_hbr_link,
  IF(link LIKE '%skooldio.com%', 1, 0) AS is_skooldio_link,
  IF(link LIKE '%facebook.com%', 1, 0) AS is_facebook_link,
  IF(
    REGEXP_CONTAINS(link, r'youtu.be|youtube.com'), 
    1, 0
  ) AS is_youtube_link,
  IF(
    REGEXP_CONTAINS(
      LOWER(message), 
      r'ข้อมูล|data|machine( )*learning|ai|deep learning'
    ), 
    1, 0
  ) AS is_about_data,
  IF(
    REGEXP_CONTAINS(
      LOWER(message), 
      r'ออกแบบ|design|ui|ux'
    ), 
    1, 0
  ) AS is_about_design
FROM skooldio.posts
WHERE TIMESTAMP_SECONDS(created_time) < TIMESTAMP(DATE '2018-06-01', 'Asia/Bangkok')