BigQueryML2.sql

#standardSQL
SELECT
  *
FROM
  ML.EVALUATE(MODEL skooldio.popularity, (
    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')
  )
)