Sql Queries

1---2name: sql-queries3description: Write correct, performant SQL across all major data warehouse dialects (Snowflake, BigQuery, Databricks, PostgreSQL, etc.). Use when writing queries, optimizing slow SQL, translating between dialects, or building complex analytical queries with CTEs, window functions, or aggregations.4user-invocable: false5---6 7# SQL Queries Skill8 9Write correct, performant, readable SQL across all major data warehouse dialects.10 11## Dialect-Specific Reference12 13### PostgreSQL (including Aurora, RDS, Supabase, Neon)14 15**Date/time:**16```sql17-- Current date/time18CURRENT_DATE, CURRENT_TIMESTAMP, NOW()19 20-- Date arithmetic21date_column + INTERVAL '7 days'22date_column - INTERVAL '1 month'23 24-- Truncate to period25DATE_TRUNC('month', created_at)26 27-- Extract parts28EXTRACT(YEAR FROM created_at)29EXTRACT(DOW FROM created_at)  -- 0=Sunday30 31-- Format32TO_CHAR(created_at, 'YYYY-MM-DD')33```34 35**String functions:**36```sql37-- Concatenation38first_name || ' ' || last_name39CONCAT(first_name, ' ', last_name)40 41-- Pattern matching42column ILIKE '%pattern%'  -- case-insensitive43column ~ '^regex_pattern$'  -- regex44 45-- String manipulation46LEFT(str, n), RIGHT(str, n)47SPLIT_PART(str, delimiter, position)48REGEXP_REPLACE(str, pattern, replacement)49```50 51**Arrays and JSON:**52```sql53-- JSON access54data->>'key'  -- text55data->'nested'->'key'  -- json56data#>>'{path,to,key}'  -- nested text57 58-- Array operations59ARRAY_AGG(column)60ANY(array_column)61array_column @> ARRAY['value']62```63 64**Performance tips:**65- Use `EXPLAIN ANALYZE` to profile queries66- Create indexes on frequently filtered/joined columns67- Use `EXISTS` over `IN` for correlated subqueries68- Partial indexes for common filter conditions69- Use connection pooling for concurrent access70 71---72 73### Snowflake74 75**Date/time:**76```sql77-- Current date/time78CURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()79 80-- Date arithmetic81DATEADD(day, 7, date_column)82DATEDIFF(day, start_date, end_date)83 84-- Truncate to period85DATE_TRUNC('month', created_at)86 87-- Extract parts88YEAR(created_at), MONTH(created_at), DAY(created_at)89DAYOFWEEK(created_at)90 91-- Format92TO_CHAR(created_at, 'YYYY-MM-DD')93```94 95**String functions:**96```sql97-- Case-insensitive by default (depends on collation)98column ILIKE '%pattern%'99REGEXP_LIKE(column, 'pattern')100 101-- Parse JSON102column:key::string  -- dot notation for VARIANT103PARSE_JSON('{"key": "value"}')104GET_PATH(variant_col, 'path.to.key')105 106-- Flatten arrays/objects107SELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f108```109 110**Semi-structured data:**111```sql112-- VARIANT type access113data:customer:name::STRING114data:items[0]:price::NUMBER115 116-- Flatten nested structures117SELECT118    t.id,119    item.value:name::STRING as item_name,120    item.value:qty::NUMBER as quantity121FROM my_table t,122LATERAL FLATTEN(input => t.data:items) item123```124 125**Performance tips:**126- Use clustering keys on large tables (not traditional indexes)127- Filter on clustering key columns for partition pruning128- Set appropriate warehouse size for query complexity129- Use `RESULT_SCAN(LAST_QUERY_ID())` to avoid re-running expensive queries130- Use transient tables for staging/temp data131 132---133 134### BigQuery (Google Cloud)135 136**Date/time:**137```sql138-- Current date/time139CURRENT_DATE(), CURRENT_TIMESTAMP()140 141-- Date arithmetic142DATE_ADD(date_column, INTERVAL 7 DAY)143DATE_SUB(date_column, INTERVAL 1 MONTH)144DATE_DIFF(end_date, start_date, DAY)145TIMESTAMP_DIFF(end_ts, start_ts, HOUR)146 147-- Truncate to period148DATE_TRUNC(created_at, MONTH)149TIMESTAMP_TRUNC(created_at, HOUR)150 151-- Extract parts152EXTRACT(YEAR FROM created_at)153EXTRACT(DAYOFWEEK FROM created_at)  -- 1=Sunday154 155-- Format156FORMAT_DATE('%Y-%m-%d', date_column)157FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts_column)158```159 160**String functions:**161```sql162-- No ILIKE, use LOWER()163LOWER(column) LIKE '%pattern%'164REGEXP_CONTAINS(column, r'pattern')165REGEXP_EXTRACT(column, r'pattern')166 167-- String manipulation168SPLIT(str, delimiter)  -- returns ARRAY169ARRAY_TO_STRING(array, delimiter)170```171 172**Arrays and structs:**173```sql174-- Array operations175ARRAY_AGG(column)176UNNEST(array_column)177ARRAY_LENGTH(array_column)178value IN UNNEST(array_column)179 180-- Struct access181struct_column.field_name182```183 184**Performance tips:**185- Always filter on partition columns (usually date) to reduce bytes scanned186- Use clustering for frequently filtered columns within partitions187- Use `APPROX_COUNT_DISTINCT()` for large-scale cardinality estimates188- Avoid `SELECT *` -- billing is per-byte scanned189- Use `DECLARE` and `SET` for parameterized scripts190- Preview query cost with dry run before executing large queries191 192---193 194### Redshift (Amazon)195 196**Date/time:**197```sql198-- Current date/time199CURRENT_DATE, GETDATE(), SYSDATE200 201-- Date arithmetic202DATEADD(day, 7, date_column)203DATEDIFF(day, start_date, end_date)204 205-- Truncate to period206DATE_TRUNC('month', created_at)207 208-- Extract parts209EXTRACT(YEAR FROM created_at)210DATE_PART('dow', created_at)211```212 213**String functions:**214```sql215-- Case-insensitive216column ILIKE '%pattern%'217REGEXP_INSTR(column, 'pattern') > 0218 219-- String manipulation220SPLIT_PART(str, delimiter, position)221LISTAGG(column, ', ') WITHIN GROUP (ORDER BY column)222```223 224**Performance tips:**225- Design distribution keys for collocated joins (DISTKEY)226- Use sort keys for frequently filtered columns (SORTKEY)227- Use `EXPLAIN` to check query plan228- Avoid cross-node data movement (watch for DS_BCAST and DS_DIST)229- `ANALYZE` and `VACUUM` regularly230- Use late-binding views for schema flexibility231 232---233 234### Databricks SQL235 236**Date/time:**237```sql238-- Current date/time239CURRENT_DATE(), CURRENT_TIMESTAMP()240 241-- Date arithmetic242DATE_ADD(date_column, 7)243DATEDIFF(end_date, start_date)244ADD_MONTHS(date_column, 1)245 246-- Truncate to period247DATE_TRUNC('MONTH', created_at)248TRUNC(date_column, 'MM')249 250-- Extract parts251YEAR(created_at), MONTH(created_at)252DAYOFWEEK(created_at)253```254 255**Delta Lake features:**256```sql257-- Time travel258SELECT * FROM my_table TIMESTAMP AS OF '2024-01-15'259SELECT * FROM my_table VERSION AS OF 42260 261-- Describe history262DESCRIBE HISTORY my_table263 264-- Merge (upsert)265MERGE INTO target USING source266ON target.id = source.id267WHEN MATCHED THEN UPDATE SET *268WHEN NOT MATCHED THEN INSERT *269```270 271**Performance tips:**272- Use Delta Lake's `OPTIMIZE` and `ZORDER` for query performance273- Leverage Photon engine for compute-intensive queries274- Use `CACHE TABLE` for frequently accessed datasets275- Partition by low-cardinality date columns276 277---278 279## Common SQL Patterns280 281### Window Functions282 283```sql284-- Ranking285ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)286RANK() OVER (PARTITION BY category ORDER BY revenue DESC)287DENSE_RANK() OVER (ORDER BY score DESC)288 289-- Running totals / moving averages290SUM(revenue) OVER (ORDER BY date_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total291AVG(revenue) OVER (ORDER BY date_col ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d292 293-- Lag / Lead294LAG(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as prev_value295LEAD(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as next_value296 297-- First / Last value298FIRST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)299LAST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)300 301-- Percent of total302revenue / SUM(revenue) OVER () as pct_of_total303revenue / SUM(revenue) OVER (PARTITION BY category) as pct_of_category304```305 306### CTEs for Readability307 308```sql309WITH310-- Step 1: Define the base population311base_users AS (312    SELECT user_id, created_at, plan_type313    FROM users314    WHERE created_at >= DATE '2024-01-01'315      AND status = 'active'316),317 318-- Step 2: Calculate user-level metrics319user_metrics AS (320    SELECT321        u.user_id,322        u.plan_type,323        COUNT(DISTINCT e.session_id) as session_count,324        SUM(e.revenue) as total_revenue325    FROM base_users u326    LEFT JOIN events e ON u.user_id = e.user_id327    GROUP BY u.user_id, u.plan_type328),329 330-- Step 3: Aggregate to summary level331summary AS (332    SELECT333        plan_type,334        COUNT(*) as user_count,335        AVG(session_count) as avg_sessions,336        SUM(total_revenue) as total_revenue337    FROM user_metrics338    GROUP BY plan_type339)340 341SELECT * FROM summary ORDER BY total_revenue DESC;342```343 344### Cohort Retention345 346```sql347WITH cohorts AS (348    SELECT349        user_id,350        DATE_TRUNC('month', first_activity_date) as cohort_month351    FROM users352),353activity AS (354    SELECT355        user_id,356        DATE_TRUNC('month', activity_date) as activity_month357    FROM user_activity358)359SELECT360    c.cohort_month,361    COUNT(DISTINCT c.user_id) as cohort_size,362    COUNT(DISTINCT CASE363        WHEN a.activity_month = c.cohort_month THEN a.user_id364    END) as month_0,365    COUNT(DISTINCT CASE366        WHEN a.activity_month = c.cohort_month + INTERVAL '1 month' THEN a.user_id367    END) as month_1,368    COUNT(DISTINCT CASE369        WHEN a.activity_month = c.cohort_month + INTERVAL '3 months' THEN a.user_id370    END) as month_3371FROM cohorts c372LEFT JOIN activity a ON c.user_id = a.user_id373GROUP BY c.cohort_month374ORDER BY c.cohort_month;375```376 377### Funnel Analysis378 379```sql380WITH funnel AS (381    SELECT382        user_id,383        MAX(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) as step_1_view,384        MAX(CASE WHEN event = 'signup_start' THEN 1 ELSE 0 END) as step_2_start,385        MAX(CASE WHEN event = 'signup_complete' THEN 1 ELSE 0 END) as step_3_complete,386        MAX(CASE WHEN event = 'first_purchase' THEN 1 ELSE 0 END) as step_4_purchase387    FROM events388    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'389    GROUP BY user_id390)391SELECT392    COUNT(*) as total_users,393    SUM(step_1_view) as viewed,394    SUM(step_2_start) as started_signup,395    SUM(step_3_complete) as completed_signup,396    SUM(step_4_purchase) as purchased,397    ROUND(100.0 * SUM(step_2_start) / NULLIF(SUM(step_1_view), 0), 1) as view_to_start_pct,398    ROUND(100.0 * SUM(step_3_complete) / NULLIF(SUM(step_2_start), 0), 1) as start_to_complete_pct,399    ROUND(100.0 * SUM(step_4_purchase) / NULLIF(SUM(step_3_complete), 0), 1) as complete_to_purchase_pct400FROM funnel;401```402 403### Deduplication404 405```sql406-- Keep the most recent record per key407WITH ranked AS (408    SELECT409        *,410        ROW_NUMBER() OVER (411            PARTITION BY entity_id412            ORDER BY updated_at DESC413        ) as rn414    FROM source_table415)416SELECT * FROM ranked WHERE rn = 1;417```418 419## Error Handling and Debugging420 421When a query fails:422 4231. **Syntax errors**: Check for dialect-specific syntax (e.g., `ILIKE` not available in BigQuery, `SAFE_DIVIDE` only in BigQuery)4242. **Column not found**: Verify column names against schema -- check for typos, case sensitivity (PostgreSQL is case-sensitive for quoted identifiers)4253. **Type mismatches**: Cast explicitly when comparing different types (`CAST(col AS DATE)`, `col::DATE`)4264. **Division by zero**: Use `NULLIF(denominator, 0)` or dialect-specific safe division4275. **Ambiguous columns**: Always qualify column names with table alias in JOINs4286. **Group by errors**: All non-aggregated columns must be in GROUP BY (except in BigQuery which allows grouping by alias)