GoogleSQL

GoogleSQL Cheat Sheets

Compact references for common BigQuery work: dates, arrays, JSON, aggregations, and window functions.

Dates

  • DATE_TRUNC(order_date, MONTH)
  • DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  • EXTRACT(ISOWEEK FROM order_date)

Arrays

  • CROSS JOIN UNNEST(items) AS item
  • ARRAY_AGG(product_id ORDER BY revenue DESC)
  • ARRAY_LENGTH(event_params)

Windows

  • ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)
  • SUM(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
  • LAG(revenue) OVER (PARTITION BY channel ORDER BY week)

JSON

  • JSON_VALUE(payload, '$.campaign')
  • SAFE_CAST(JSON_VALUE(payload, '$.amount') AS NUMERIC)
  • JSON_QUERY(payload, '$.items')