Sql Queries

# SQL Queries Skill

Write correct, performant, readable SQL across all major data warehouse dialects.

## Dialect-Specific Reference

### PostgreSQL (including Aurora, RDS, Supabase, Neon)

**Date/time:** ```sql -- 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:** ```sql -- 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:** ```sql -- 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:** - Use `EXPLAIN ANALYZE` to profile queries - Create indexes on frequently filtered/joined columns - Use `EXISTS` over `IN` for correlated subqueries - Partial indexes for common filter conditions - Use connection pooling for concurrent access

---

### Snowflake

**Date/time:** ```sql -- 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:** ```sql -- Case-insensitive by default (depends on collation) column ILIKE '%pattern%' REGEXP_LIKE(column, 'pattern')

-- Parse JSON column:key::string -- dot notation for VARIANT PARSE_JSON('{"key": "value"}') GET_PATH(variant_col, 'path.to.key')

-- Flatten arrays/objects SELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f ```

**Semi-structured data:** ```sql -- VARIANT type access data:customer:name::STRING data:items[0]:price::NUMBER

-- Flatten nested structures SELECT t.id, item.value:name::STRING as item_name, item.value:qty::NUMBER as quantity FROM my_table t, LATERAL FLATTEN(input => t.data:items) item ```

**Performance tips:** - Use clustering keys on large tables (not traditional indexes) - Filter on clustering key columns for partition pruning - Set appropriate warehouse size for query complexity - Use `RESULT_SCAN(LAST_QUERY_ID())` to avoid re-running expensive queries - Use transient tables for staging/temp data

---

### BigQuery (Google Cloud)

**Date/time:** ```sql -- Current date/time CURRENT_DATE(), CURRENT_TIMESTAMP()

-- Date arithmetic DATE_ADD(date_column, INTERVAL 7 DAY) DATE_SUB(date_column, INTERVAL 1 MONTH) DATE_DIFF(end_date, start_date, DAY) TIMESTAMP_DIFF(end_ts, start_ts, HOUR)

-- Truncate to period DATE_TRUNC(created_at, MONTH) TIMESTAMP_TRUNC(created_at, HOUR)

-- Extract parts EXTRACT(YEAR FROM created_at) EXTRACT(DAYOFWEEK FROM created_at) -- 1=Sunday

-- Format FORMAT_DATE('%Y-%m-%d', date_column) FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts_column) ```

**String functions:** ```sql -- No ILIKE, use LOWER() LOWER(column) LIKE '%pattern%' REGEXP_CONTAINS(column, r'pattern') REGEXP_EXTRACT(column, r'pattern')

-- String manipulation SPLIT(str, delimiter) -- returns ARRAY ARRAY_TO_STRING(array, delimiter) ```

**Arrays and structs:** ```sql -- Array operations ARRAY_AGG(column) UNNEST(array_column) ARRAY_LENGTH(array_column) value IN UNNEST(array_column)

-- Struct access struct_column.field_name ```

**Performance tips:** - Always filter on partition columns (usually date) to reduce bytes scanned - Use clustering for frequently filtered columns within partitions - Use `APPROX_COUNT_DISTINCT()` for large-scale cardinality estimates - Avoid `SELECT *` -- billing is per-byte scanned - Use `DECLARE` and `SET` for parameterized scripts - Preview query cost with dry run before executing large queries

---

### Redshift (Amazon)

**Date/time:** ```sql -- Current date/time CURRENT_DATE, GETDATE(), SYSDATE

-- Date arithmetic DATEADD(day, 7, date_column) DATEDIFF(day, start_date, end_date)

-- Truncate to period DATE_TRUNC('month', created_at)

-- Extract parts EXTRACT(YEAR FROM created_at) DATE_PART('dow', created_at) ```

**String functions:** ```sql -- Case-insensitive column ILIKE '%pattern%' REGEXP_INSTR(column, 'pattern') > 0

-- String manipulation SPLIT_PART(str, delimiter, position) LISTAGG(column, ', ') WITHIN GROUP (ORDER BY column) ```

**Performance tips:** - Design distribution keys for collocated joins (DISTKEY) - Use sort keys for frequently filtered columns (SORTKEY) - Use `EXPLAIN` to check query plan - Avoid cross-node data movement (watch for DS_BCAST and DS