Write correct, performant BigQuery SQL for FashionUnited data analysis. Includes templates for job posting metrics, editorial analytics, advertising performance, marketplace catalog, and Top 100 indices. Also supports other SQL dialects when needed.
Write correct, performant, readable SQL across all major data warehouse dialects.
Date/time:
-- Current date/time
CURRENT_DATE, CURRENT_TIMESTAMP, NOW()
-- Date arithmetic
date_column + INTERVAL '7 days'
date_column - INTERVAL '1 month'
-- Truncate to period
DATE_TRUNC('month', created_at)
-- Extract parts
EXTRACT(YEAR FROM created_at)
EXTRACT(DOW FROM created_at) -- 0=Sunday
-- Format
TO_CHAR(created_at, 'YYYY-MM-DD')
String functions:
-- Concatenation
first_name || ' ' || last_name
CONCAT(first_name, ' ', last_name)
-- Pattern matching
column ILIKE '%pattern%' -- case-insensitive
column ~ '^regex_pattern$' -- regex
-- String manipulation
LEFT(str, n), RIGHT(str, n)
SPLIT_PART(str, delimiter, position)
REGEXP_REPLACE(str, pattern, replacement)
Arrays and JSON:
-- JSON access
data->>'key' -- text
data->'nested'->'key' -- json
data#>>'{path,to,key}' -- nested text
-- Array operations
ARRAY_AGG(column)
ANY(array_column)
array_column @> ARRAY['value']
Performance tips:
EXPLAIN ANALYZE to profile queriesEXISTS over IN for correlated subqueriesDate/time:
-- Current date/time
CURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()
-- Date arithmetic
DATEADD(day, 7, date_column)
DATEDIFF(day, start_date, end_date)
-- Truncate to period
DATE_TRUNC('month', created_at)
-- Extract parts
YEAR(created_at), MONTH(created_at), DAY(created_at)
DAYOFWEEK(created_at)
-- Format
TO_CHAR(created_at, 'YYYY-MM-DD')
String functions:
-- Case-insensitive by default (depends on collation)
column ILIKE '%pattern%'
REGEXP_LIKE(column, 'pattern')
-- Parse JSON