BigQuery functions: a map of the main families, with examples

The table used in every example

Paste this in front of any query below and it runs as is, without a table of your own:

WITH orders AS (
  SELECT 1 AS order_id, 'anna' AS customer,
         DATE '2026-09-01' AS order_date,
         120.0 AS amount, 'paid' AS status
  UNION ALL SELECT 2, 'anna', DATE '2026-09-15', 80.0, 'paid'
  UNION ALL SELECT 3, 'ben', DATE '2026-09-20', 200.0, 'refunded'
  UNION ALL SELECT 4, 'ben', DATE '2026-10-02', 50.0, 'paid'
  UNION ALL SELECT 5, 'cara', DATE '2026-10-05', NULL, 'paid'
)

Five orders, three customers, and one order with no amount, because real tables have those.

Aggregate functions

They turn many rows into one.

SELECT
  COUNT(*) AS orders,
  COUNT(amount) AS with_amount,
  SUM(amount) AS total,
  AVG(amount) AS avg_amount
FROM orders;

The trap: COUNT(*) counts rows, COUNT(amount) counts rows where amount is not NULL, and AVG divides by the second number. The average here is 450 / 4, not 450 / 5. More in aggregate functions and statistical aggregates.

Window functions

They calculate across related rows and keep every row.

SELECT
  order_id, customer, amount,
  SUM(amount) OVER (
    PARTITION BY customer ORDER BY order_date
  ) AS running,
  ROW_NUMBER() OVER (
    PARTITION BY customer ORDER BY order_date
  ) AS rn
FROM orders;

rn = 1 is each customer’s first order, the usual way to pick one row per group. To filter on it without a subquery, use QUALIFY; see WHERE, HAVING and QUALIFY. The family splits into numbering and navigation functions.

Date and time functions

SELECT
  FORMAT_DATE('%Y-%m',
    DATE_TRUNC(order_date, MONTH)) AS month,
  SUM(amount) AS total
FROM orders
GROUP BY month
ORDER BY month;

The trap: there are three time types, and the functions are named after them. A TIMESTAMP is truncated in UTC unless you name a time zone, so a late-evening order can land in the next day or month. Which type you have decides which guide you need: date, datetime or timestamp functions. The difference between the types is in DATE, DATETIME and TIMESTAMP.

String functions

The trap: CONCAT with a NULL argument returns NULL, and comparisons are case-sensitive. The string functions guide has both.

Conversion functions

CAST fails the whole query on a value it cannot convert. SAFE_CAST returns NULL for that value and carries on. That is convenient, and it also hides bad data, so count the NULLs it produced. See CAST and SAFE_CAST and the conversion functions lookup.

Conditional expressions

SELECT
  order_id,
  IF(status = 'paid', 'keep', 'drop') AS flag,
  COALESCE(amount, 0) AS amount0,
  CASE
    WHEN amount >= 100 THEN 'large'
    WHEN amount IS NULL THEN 'unknown'
    ELSE 'small'
  END AS size
FROM orders;

The trap: CASE stops at the first match, so order the branches from specific to general. And COALESCE(amount, 0) changes an average: order 5 now counts as a zero. More in conditional expressions and COALESCE.

JSON functions

JSON_VALUE returns a scalar as a string; JSON_QUERY returns an object or an array. Picking the wrong one returns NULL without an error. See JSON parsing functions.

Array functions

UNNEST turns an array into rows. It is how you read repeated fields such as the event parameters in a GA4 export. Unnesting two arrays in one query multiplies rows; the array functions guide shows how to avoid it.

Math functions

A plain 10 / 0 fails the query. Any ratio built from real data, such as a conversion rate or cost per click, should use SAFE_DIVIDE. See mathematical functions.

Where functions stop helping

Functions answer “how do I write this”. They do not answer “which of our four versions of this query is right”. Two analysts can both use COALESCE correctly and still report different revenue, because one of them replaced missing amounts with zero and the other left them out.

That is a question of ownership, not syntax. In OWOX Data Marts a query is published once as a Data Mart, with a description on every column that says what was decided, and reports in Google Sheets or a dashboard read that one definition. OWOX Data Marts runs the SQL as you wrote it, in your own warehouse, and does not transform the data.

For how the clauses fit together, start with the BigQuery SQL guide. For complete queries to adapt, see BigQuery query examples.

Google BigQueryComprehensive Guide to BigQuery Aggregate Functions for 2025April 18, 2024→Google BigQueryBigQuery array functions: syntax and tested examplesOctober 6, 2026→Google BigQueryBigQuery CAST and SAFE_CAST: convert data types without errorsOctober 6, 2026→

See all articles →