Write correct, performant, readable SQL across all major data warehouse dialects.
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