Functions and types

Use these patterns in the SQL workbench. Function names in other connected sources can differ.

Aggregate values

COUNT(*) counts rows, including rows with null values.
COUNT(column) counts non-null values in that column.
COUNT(DISTINCT column) counts unique non-null values.
SUM, AVG, MIN, and MAX summarize values.

SELECT COUNT(*) AS rows,
       COUNT(customer_id) AS rows_with_customer,
       COUNT(DISTINCT customer_id) AS unique_customers,
       SUM(revenue) AS total_revenue
FROM lake.uploads.orders;

Conditional counts

Use CASE inside an aggregate for conditional counts. Lake does not use BigQuery’s COUNTIF syntax.

SELECT SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM lake.uploads.orders;

Nulls and safe division

COALESCE(value, fallback) supplies a default for a null value. NULLIF(value, 0) returns null when the denominator is zero.

SELECT SUM(revenue) / NULLIF(COUNT(DISTINCT customer_id), 0)
         AS revenue_per_customer
FROM lake.uploads.orders
WHERE status = 'completed';

Null can mean unknown or unavailable; do not automatically turn it into zero unless that matches the business definition.

Cast values

CAST converts a value to a type. The FastBI SAFE_NUMBER function returns null for invalid numeric text.

SELECT order_id,
       SAFE_NUMBER(revenue_text) AS revenue
FROM lake.uploads.imported_orders
LIMIT 100;

Check failed conversions before aggregating: aggregates can silently omit null values. When applying string functions to numeric identifiers, cast first, for example substr(CAST(customer_id AS VARCHAR), 1, 3). A cast cannot restore leading zeros already lost during ingestion.

Dates

For daily totals, a date cast can group timestamps by day:

SELECT CAST(order_timestamp AS DATE) AS day,
       SUM(revenue) AS revenue
FROM lake.uploads.orders
GROUP BY CAST(order_timestamp AS DATE)
ORDER BY day
LIMIT 1000;

Confirm timestamp storage and timezone semantics before grouping. Prefer a source field already normalized to the reporting timezone when available.

Text and regular expressions

Use LOWER, UPPER, and TRIM to normalize text. For grouped text, deduplicate input in a CTE before applying STRING_AGG.

Continue with scalar functions and aggregate functions.