sql-queries — Skillopedia

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: String functions: Arrays and JSON: Performance tips: - Use to profile queries - Create indexes on frequently filtered/joined columns - Use over for correlated subqueries - Partial indexes for common filter conditions - Use connection pooling for concurrent access --- Snowflake Date/time: String functions: Semi-structured data: Performance tips: - Use clustering keys on large tables (not traditional indexes)…

-- regex\n\n-- String manipulation\nLEFT(str, n), RIGHT(str, n)\nSPLIT_PART(str, delimiter, position)\nREGEXP_REPLACE(str, pattern, replacement)\n```\n\n**Arrays and JSON:**\n```sql\n-- JSON access\ndata->>'key' -- text\ndata->'nested'->'key' -- json\ndata#>>'{path,to,key}' -- nested text\n\n-- Array operations\nARRAY_AGG(column)\nANY(array_column)\narray_column @> ARRAY['value']\n```\n\n**Performance tips:**\n- Use `EXPLAIN ANALYZE` to profile queries\n- Create indexes on frequently filtered/joined columns\n- Use `EXISTS` over `IN` for correlated subqueries\n- Partial indexes for common filter conditions\n- Use connection pooling for concurrent access\n\n---\n\n### Snowflake\n\n**Date/time:**\n```sql\n-- Current date/time\nCURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()\n\n-- Date arithmetic\nDATEADD(day, 7, date_column)\nDATEDIFF(day, start_date, end_date)\n\n-- Truncate to period\nDATE_TRUNC('month', created_at)\n\n-- Extract parts\nYEAR(created_at), MONTH(created_at), DAY(created_at)\nDAYOFWEEK(created_at)\n\n-- Format\nTO_CHAR(created_at, 'YYYY-MM-DD')\n```\n\n**String functions:**\n```sql\n-- Case-insensitive by default (depends on collation)\ncolumn ILIKE '%pattern%'\nREGEXP_LIKE(column, 'pattern')\n\n-- Parse JSON\ncolumn:key::string -- dot notation for VARIANT\nPARSE_JSON('{\"key\": \"value\"}')\nGET_PATH(variant_col, 'path.to.key')\n\n-- Flatten arrays/objects\nSELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f\n```\n\n**Semi-structured data:**\n```sql\n-- VARIANT type access\ndata:customer:name::STRING\ndata:items[0]:price::NUMBER\n\n-- Flatten nested structures\nSELECT\n t.id,\n item.value:name::STRING as item_name,\n item.value:qty::NUMBER as quantity\nFROM my_table t,\nLATERAL FLATTEN(input => t.data:items) item\n```\n\n**Performance tips:**\n- Use clustering keys on large tables (not traditional indexes)\n- Filter on clustering key columns for partition pruning\n- Set appropriate warehouse size for query complexity\n- Use `RESULT_SCAN(LAST_QUERY_ID())` to avoid re-running expensive queries\n- Use transient tables for staging/temp data\n\n---\n\n### BigQuery (Google Cloud)\n\n**Date/time:**\n```sql\n-- Current date/time\nCURRENT_DATE(), CURRENT_TIMESTAMP()\n\n-- Date arithmetic\nDATE_ADD(date_column, INTERVAL 7 DAY)\nDATE_SUB(date_column, INTERVAL 1 MONTH)\nDATE_DIFF(end_date, start_date, DAY)\nTIMESTAMP_DIFF(end_ts, start_ts, HOUR)\n\n-- Truncate to period\nDATE_TRUNC(created_at, MONTH)\nTIMESTAMP_TRUNC(created_at, HOUR)\n\n-- Extract parts\nEXTRACT(YEAR FROM created_at)\nEXTRACT(DAYOFWEEK FROM created_at) -- 1=Sunday\n\n-- Format\nFORMAT_DATE('%Y-%m-%d', date_column)\nFORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts_column)\n```\n\n**String functions:**\n```sql\n-- No ILIKE, use LOWER()\nLOWER(column) LIKE '%pattern%'\nREGEXP_CONTAINS(column, r'pattern')\nREGEXP_EXTRACT(column, r'pattern')\n\n-- String manipulation\nSPLIT(str, delimiter) -- returns ARRAY\nARRAY_TO_STRING(array, delimiter)\n```\n\n**Arrays and structs:**\n```sql\n-- Array operations\nARRAY_AGG(column)\nUNNEST(array_column)\nARRAY_LENGTH(array_column)\nvalue IN UNNEST(array_column)\n\n-- Struct access\nstruct_column.field_name\n```\n\n**Performance tips:**\n- Always filter on partition columns (usually date) to reduce bytes scanned\n- Use clustering for frequently filtered columns within partitions\n- Use `APPROX_COUNT_DISTINCT()` for large-scale cardinality estimates\n- Avoid `SELECT *` -- billing is per-byte scanned\n- Use `DECLARE` and `SET` for parameterized scripts\n- Preview query cost with dry run before executing large queries\n\n---\n\n### Redshift (Amazon)\n\n**Date/time:**\n```sql\n-- Current date/time\nCURRENT_DATE, GETDATE(), SYSDATE\n\n-- Date arithmetic\nDATEADD(day, 7, date_column)\nDATEDIFF(day, start_date, end_date)\n\n-- Truncate to period\nDATE_TRUNC('month', created_at)\n\n-- Extract parts\nEXTRACT(YEAR FROM created_at)\nDATE_PART('dow', created_at)\n```\n\n**String functions:**\n```sql\n-- Case-insensitive\ncolumn ILIKE '%pattern%'\nREGEXP_INSTR(column, 'pattern') > 0\n\n-- String manipulation\nSPLIT_PART(str, delimiter, position)\nLISTAGG(column, ', ') WITHIN GROUP (ORDER BY column)\n```\n\n**Performance tips:**\n- Design distribution keys for collocated joins (DISTKEY)\n- Use sort keys for frequently filtered columns (SORTKEY)\n- Use `EXPLAIN` to check query plan\n- Avoid cross-node data movement (watch for DS_BCAST and DS_DIST)\n- `ANALYZE` and `VACUUM` regularly\n- Use late-binding views for schema flexibility\n\n---\n\n### Databricks SQL\n\n**Date/time:**\n```sql\n-- Current date/time\nCURRENT_DATE(), CURRENT_TIMESTAMP()\n\n-- Date arithmetic\nDATE_ADD(date_column, 7)\nDATEDIFF(end_date, start_date)\nADD_MONTHS(date_column, 1)\n\n-- Truncate to period\nDATE_TRUNC('MONTH', created_at)\nTRUNC(date_column, 'MM')\n\n-- Extract parts\nYEAR(created_at), MONTH(created_at)\nDAYOFWEEK(created_at)\n```\n\n**Delta Lake features:**\n```sql\n-- Time travel\nSELECT * FROM my_table TIMESTAMP AS OF '2024-01-15'\nSELECT * FROM my_table VERSION AS OF 42\n\n-- Describe history\nDESCRIBE HISTORY my_table\n\n-- Merge (upsert)\nMERGE INTO target USING source\nON target.id = source.id\nWHEN MATCHED THEN UPDATE SET *\nWHEN NOT MATCHED THEN INSERT *\n```\n\n**Performance tips:**\n- Use Delta Lake's `OPTIMIZE` and `ZORDER` for query performance\n- Leverage Photon engine for compute-intensive queries\n- Use `CACHE TABLE` for frequently accessed datasets\n- Partition by low-cardinality date columns\n\n---\n\n## Common SQL Patterns\n\n### Window Functions\n\n```sql\n-- Ranking\nROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)\nRANK() OVER (PARTITION BY category ORDER BY revenue DESC)\nDENSE_RANK() OVER (ORDER BY score DESC)\n\n-- Running totals / moving averages\nSUM(revenue) OVER (ORDER BY date_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total\nAVG(revenue) OVER (ORDER BY date_col ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d\n\n-- Lag / Lead\nLAG(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as prev_value\nLEAD(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as next_value\n\n-- First / Last value\nFIRST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)\nLAST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)\n\n-- Percent of total\nrevenue / SUM(revenue) OVER () as pct_of_total\nrevenue / SUM(revenue) OVER (PARTITION BY category) as pct_of_category\n```\n\n### CTEs for Readability\n\n```sql\nWITH\n-- Step 1: Define the base population\nbase_users AS (\n SELECT user_id, created_at, plan_type\n FROM users\n WHERE created_at >= DATE '2024-01-01'\n AND status = 'active'\n),\n\n-- Step 2: Calculate user-level metrics\nuser_metrics AS (\n SELECT\n u.user_id,\n u.plan_type,\n COUNT(DISTINCT e.session_id) as session_count,\n SUM(e.revenue) as total_revenue\n FROM base_users u\n LEFT JOIN events e ON u.user_id = e.user_id\n GROUP BY u.user_id, u.plan_type\n),\n\n-- Step 3: Aggregate to summary level\nsummary AS (\n SELECT\n plan_type,\n COUNT(*) as user_count,\n AVG(session_count) as avg_sessions,\n SUM(total_revenue) as total_revenue\n FROM user_metrics\n GROUP BY plan_type\n)\n\nSELECT * FROM summary ORDER BY total_revenue DESC;\n```\n\n### Cohort Retention\n\n```sql\nWITH cohorts AS (\n SELECT\n user_id,\n DATE_TRUNC('month', first_activity_date) as cohort_month\n FROM users\n),\nactivity AS (\n SELECT\n user_id,\n DATE_TRUNC('month', activity_date) as activity_month\n FROM user_activity\n)\nSELECT\n c.cohort_month,\n COUNT(DISTINCT c.user_id) as cohort_size,\n COUNT(DISTINCT CASE\n WHEN a.activity_month = c.cohort_month THEN a.user_id\n END) as month_0,\n COUNT(DISTINCT CASE\n WHEN a.activity_month = c.cohort_month + INTERVAL '1 month' THEN a.user_id\n END) as month_1,\n COUNT(DISTINCT CASE\n WHEN a.activity_month = c.cohort_month + INTERVAL '3 months' THEN a.user_id\n END) as month_3\nFROM cohorts c\nLEFT JOIN activity a ON c.user_id = a.user_id\nGROUP BY c.cohort_month\nORDER BY c.cohort_month;\n```\n\n### Funnel Analysis\n\n```sql\nWITH funnel AS (\n SELECT\n user_id,\n MAX(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) as step_1_view,\n MAX(CASE WHEN event = 'signup_start' THEN 1 ELSE 0 END) as step_2_start,\n MAX(CASE WHEN event = 'signup_complete' THEN 1 ELSE 0 END) as step_3_complete,\n MAX(CASE WHEN event = 'first_purchase' THEN 1 ELSE 0 END) as step_4_purchase\n FROM events\n WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'\n GROUP BY user_id\n)\nSELECT\n COUNT(*) as total_users,\n SUM(step_1_view) as viewed,\n SUM(step_2_start) as started_signup,\n SUM(step_3_complete) as completed_signup,\n SUM(step_4_purchase) as purchased,\n ROUND(100.0 * SUM(step_2_start) / NULLIF(SUM(step_1_view), 0), 1) as view_to_start_pct,\n ROUND(100.0 * SUM(step_3_complete) / NULLIF(SUM(step_2_start), 0), 1) as start_to_complete_pct,\n ROUND(100.0 * SUM(step_4_purchase) / NULLIF(SUM(step_3_complete), 0), 1) as complete_to_purchase_pct\nFROM funnel;\n```\n\n### Deduplication\n\n```sql\n-- Keep the most recent record per key\nWITH ranked AS (\n SELECT\n *,\n ROW_NUMBER() OVER (\n PARTITION BY entity_id\n ORDER BY updated_at DESC\n ) as rn\n FROM source_table\n)\nSELECT * FROM ranked WHERE rn = 1;\n```\n\n## Error Handling and Debugging\n\nWhen a query fails:\n\n1. **Syntax errors**: Check for dialect-specific syntax (e.g., `ILIKE` not available in BigQuery, `SAFE_DIVIDE` only in BigQuery)\n2. **Column not found**: Verify column names against schema -- check for typos, case sensitivity (PostgreSQL is case-sensitive for quoted identifiers)\n3. **Type mismatches**: Cast explicitly when comparing different types (`CAST(col AS DATE)`, `col::DATE`)\n4. **Division by zero**: Use `NULLIF(denominator, 0)` or dialect-specific safe division\n5. **Ambiguous columns**: Always qualify column names with table alias in JOINs\n6. **Group by errors**: All non-aggregated columns must be in GROUP BY (except in BigQuery which allows grouping by alias)\n---","attachment_filenames":[],"attachments":[],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"SQL Queries Skill","type":"text"}]},{"type":"paragraph","content":[{"text":"Write correct, performant, readable SQL across all major data warehouse dialects.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Dialect-Specific Reference","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"PostgreSQL (including Aurora, RDS, Supabase, Neon)","type":"text"}]},{"type":"paragraph","content":[{"text":"Date/time:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Current date/time\nCURRENT_DATE, CURRENT_TIMESTAMP, NOW()\n\n-- Date arithmetic\ndate_column + INTERVAL '7 days'\ndate_column - INTERVAL '1 month'\n\n-- Truncate to period\nDATE_TRUNC('month', created_at)\n\n-- Extract parts\nEXTRACT(YEAR FROM created_at)\nEXTRACT(DOW FROM created_at) -- 0=Sunday\n\n-- Format\nTO_CHAR(created_at, 'YYYY-MM-DD')","type":"text"}]},{"type":"paragraph","content":[{"text":"String functions:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Concatenation\nfirst_name || ' ' || last_name\nCONCAT(first_name, ' ', last_name)\n\n-- Pattern matching\ncolumn ILIKE '%pattern%' -- case-insensitive\ncolumn ~ '^regex_pattern

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.

-- regex\n\n-- String manipulation\nLEFT(str, n), RIGHT(str, n)\nSPLIT_PART(str, delimiter, position)\nREGEXP_REPLACE(str, pattern, replacement)","type":"text"}]},{"type":"paragraph","content":[{"text":"Arrays and JSON:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- JSON access\ndata->>'key' -- text\ndata->'nested'->'key' -- json\ndata#>>'{path,to,key}' -- nested text\n\n-- Array operations\nARRAY_AGG(column)\nANY(array_column)\narray_column @> ARRAY['value']","type":"text"}]},{"type":"paragraph","content":[{"text":"Performance tips:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"EXPLAIN ANALYZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" to profile queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create indexes on frequently filtered/joined columns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"EXISTS","type":"text","marks":[{"type":"code_inline"}]},{"text":" over ","type":"text"},{"text":"IN","type":"text","marks":[{"type":"code_inline"}]},{"text":" for correlated subqueries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partial indexes for common filter conditions","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use connection pooling for concurrent access","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":3},"content":[{"text":"Snowflake","type":"text"}]},{"type":"paragraph","content":[{"text":"Date/time:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Current date/time\nCURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()\n\n-- Date arithmetic\nDATEADD(day, 7, date_column)\nDATEDIFF(day, start_date, end_date)\n\n-- Truncate to period\nDATE_TRUNC('month', created_at)\n\n-- Extract parts\nYEAR(created_at), MONTH(created_at), DAY(created_at)\nDAYOFWEEK(created_at)\n\n-- Format\nTO_CHAR(created_at, 'YYYY-MM-DD')","type":"text"}]},{"type":"paragraph","content":[{"text":"String functions:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Case-insensitive by default (depends on collation)\ncolumn ILIKE '%pattern%'\nREGEXP_LIKE(column, 'pattern')\n\n-- Parse JSON\ncolumn:key::string -- dot notation for VARIANT\nPARSE_JSON('{\"key\": \"value\"}')\nGET_PATH(variant_col, 'path.to.key')\n\n-- Flatten arrays/objects\nSELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f","type":"text"}]},{"type":"paragraph","content":[{"text":"Semi-structured data:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- VARIANT type access\ndata:customer:name::STRING\ndata:items[0]:price::NUMBER\n\n-- Flatten nested structures\nSELECT\n t.id,\n item.value:name::STRING as item_name,\n item.value:qty::NUMBER as quantity\nFROM my_table t,\nLATERAL FLATTEN(input => t.data:items) item","type":"text"}]},{"type":"paragraph","content":[{"text":"Performance tips:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use clustering keys on large tables (not traditional indexes)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Filter on clustering key columns for partition pruning","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Set appropriate warehouse size for query complexity","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"RESULT_SCAN(LAST_QUERY_ID())","type":"text","marks":[{"type":"code_inline"}]},{"text":" to avoid re-running expensive queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use transient tables for staging/temp data","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":3},"content":[{"text":"BigQuery (Google Cloud)","type":"text"}]},{"type":"paragraph","content":[{"text":"Date/time:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Current date/time\nCURRENT_DATE(), CURRENT_TIMESTAMP()\n\n-- Date arithmetic\nDATE_ADD(date_column, INTERVAL 7 DAY)\nDATE_SUB(date_column, INTERVAL 1 MONTH)\nDATE_DIFF(end_date, start_date, DAY)\nTIMESTAMP_DIFF(end_ts, start_ts, HOUR)\n\n-- Truncate to period\nDATE_TRUNC(created_at, MONTH)\nTIMESTAMP_TRUNC(created_at, HOUR)\n\n-- Extract parts\nEXTRACT(YEAR FROM created_at)\nEXTRACT(DAYOFWEEK FROM created_at) -- 1=Sunday\n\n-- Format\nFORMAT_DATE('%Y-%m-%d', date_column)\nFORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts_column)","type":"text"}]},{"type":"paragraph","content":[{"text":"String functions:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- No ILIKE, use LOWER()\nLOWER(column) LIKE '%pattern%'\nREGEXP_CONTAINS(column, r'pattern')\nREGEXP_EXTRACT(column, r'pattern')\n\n-- String manipulation\nSPLIT(str, delimiter) -- returns ARRAY\nARRAY_TO_STRING(array, delimiter)","type":"text"}]},{"type":"paragraph","content":[{"text":"Arrays and structs:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Array operations\nARRAY_AGG(column)\nUNNEST(array_column)\nARRAY_LENGTH(array_column)\nvalue IN UNNEST(array_column)\n\n-- Struct access\nstruct_column.field_name","type":"text"}]},{"type":"paragraph","content":[{"text":"Performance tips:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Always filter on partition columns (usually date) to reduce bytes scanned","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use clustering for frequently filtered columns within partitions","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"APPROX_COUNT_DISTINCT()","type":"text","marks":[{"type":"code_inline"}]},{"text":" for large-scale cardinality estimates","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Avoid ","type":"text"},{"text":"SELECT *","type":"text","marks":[{"type":"code_inline"}]},{"text":" -- billing is per-byte scanned","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"DECLARE","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"SET","type":"text","marks":[{"type":"code_inline"}]},{"text":" for parameterized scripts","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Preview query cost with dry run before executing large queries","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":3},"content":[{"text":"Redshift (Amazon)","type":"text"}]},{"type":"paragraph","content":[{"text":"Date/time:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Current date/time\nCURRENT_DATE, GETDATE(), SYSDATE\n\n-- Date arithmetic\nDATEADD(day, 7, date_column)\nDATEDIFF(day, start_date, end_date)\n\n-- Truncate to period\nDATE_TRUNC('month', created_at)\n\n-- Extract parts\nEXTRACT(YEAR FROM created_at)\nDATE_PART('dow', created_at)","type":"text"}]},{"type":"paragraph","content":[{"text":"String functions:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Case-insensitive\ncolumn ILIKE '%pattern%'\nREGEXP_INSTR(column, 'pattern') > 0\n\n-- String manipulation\nSPLIT_PART(str, delimiter, position)\nLISTAGG(column, ', ') WITHIN GROUP (ORDER BY column)","type":"text"}]},{"type":"paragraph","content":[{"text":"Performance tips:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Design distribution keys for collocated joins (DISTKEY)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use sort keys for frequently filtered columns (SORTKEY)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"EXPLAIN","type":"text","marks":[{"type":"code_inline"}]},{"text":" to check query plan","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Avoid cross-node data movement (watch for DS_BCAST and DS_DIST)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"ANALYZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"VACUUM","type":"text","marks":[{"type":"code_inline"}]},{"text":" regularly","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use late-binding views for schema flexibility","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":3},"content":[{"text":"Databricks SQL","type":"text"}]},{"type":"paragraph","content":[{"text":"Date/time:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Current date/time\nCURRENT_DATE(), CURRENT_TIMESTAMP()\n\n-- Date arithmetic\nDATE_ADD(date_column, 7)\nDATEDIFF(end_date, start_date)\nADD_MONTHS(date_column, 1)\n\n-- Truncate to period\nDATE_TRUNC('MONTH', created_at)\nTRUNC(date_column, 'MM')\n\n-- Extract parts\nYEAR(created_at), MONTH(created_at)\nDAYOFWEEK(created_at)","type":"text"}]},{"type":"paragraph","content":[{"text":"Delta Lake features:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Time travel\nSELECT * FROM my_table TIMESTAMP AS OF '2024-01-15'\nSELECT * FROM my_table VERSION AS OF 42\n\n-- Describe history\nDESCRIBE HISTORY my_table\n\n-- Merge (upsert)\nMERGE INTO target USING source\nON target.id = source.id\nWHEN MATCHED THEN UPDATE SET *\nWHEN NOT MATCHED THEN INSERT *","type":"text"}]},{"type":"paragraph","content":[{"text":"Performance tips:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use Delta Lake's ","type":"text"},{"text":"OPTIMIZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" and ","type":"text"},{"text":"ZORDER","type":"text","marks":[{"type":"code_inline"}]},{"text":" for query performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Leverage Photon engine for compute-intensive queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"CACHE TABLE","type":"text","marks":[{"type":"code_inline"}]},{"text":" for frequently accessed datasets","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Partition by low-cardinality date columns","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common SQL Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Window Functions","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Ranking\nROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)\nRANK() OVER (PARTITION BY category ORDER BY revenue DESC)\nDENSE_RANK() OVER (ORDER BY score DESC)\n\n-- Running totals / moving averages\nSUM(revenue) OVER (ORDER BY date_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total\nAVG(revenue) OVER (ORDER BY date_col ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d\n\n-- Lag / Lead\nLAG(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as prev_value\nLEAD(value, 1) OVER (PARTITION BY entity ORDER BY date_col) as next_value\n\n-- First / Last value\nFIRST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)\nLAST_VALUE(status) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)\n\n-- Percent of total\nrevenue / SUM(revenue) OVER () as pct_of_total\nrevenue / SUM(revenue) OVER (PARTITION BY category) as pct_of_category","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CTEs for Readability","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"WITH\n-- Step 1: Define the base population\nbase_users AS (\n SELECT user_id, created_at, plan_type\n FROM users\n WHERE created_at >= DATE '2024-01-01'\n AND status = 'active'\n),\n\n-- Step 2: Calculate user-level metrics\nuser_metrics AS (\n SELECT\n u.user_id,\n u.plan_type,\n COUNT(DISTINCT e.session_id) as session_count,\n SUM(e.revenue) as total_revenue\n FROM base_users u\n LEFT JOIN events e ON u.user_id = e.user_id\n GROUP BY u.user_id, u.plan_type\n),\n\n-- Step 3: Aggregate to summary level\nsummary AS (\n SELECT\n plan_type,\n COUNT(*) as user_count,\n AVG(session_count) as avg_sessions,\n SUM(total_revenue) as total_revenue\n FROM user_metrics\n GROUP BY plan_type\n)\n\nSELECT * FROM summary ORDER BY total_revenue DESC;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Cohort Retention","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"WITH cohorts AS (\n SELECT\n user_id,\n DATE_TRUNC('month', first_activity_date) as cohort_month\n FROM users\n),\nactivity AS (\n SELECT\n user_id,\n DATE_TRUNC('month', activity_date) as activity_month\n FROM user_activity\n)\nSELECT\n c.cohort_month,\n COUNT(DISTINCT c.user_id) as cohort_size,\n COUNT(DISTINCT CASE\n WHEN a.activity_month = c.cohort_month THEN a.user_id\n END) as month_0,\n COUNT(DISTINCT CASE\n WHEN a.activity_month = c.cohort_month + INTERVAL '1 month' THEN a.user_id\n END) as month_1,\n COUNT(DISTINCT CASE\n WHEN a.activity_month = c.cohort_month + INTERVAL '3 months' THEN a.user_id\n END) as month_3\nFROM cohorts c\nLEFT JOIN activity a ON c.user_id = a.user_id\nGROUP BY c.cohort_month\nORDER BY c.cohort_month;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Funnel Analysis","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"WITH funnel AS (\n SELECT\n user_id,\n MAX(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) as step_1_view,\n MAX(CASE WHEN event = 'signup_start' THEN 1 ELSE 0 END) as step_2_start,\n MAX(CASE WHEN event = 'signup_complete' THEN 1 ELSE 0 END) as step_3_complete,\n MAX(CASE WHEN event = 'first_purchase' THEN 1 ELSE 0 END) as step_4_purchase\n FROM events\n WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'\n GROUP BY user_id\n)\nSELECT\n COUNT(*) as total_users,\n SUM(step_1_view) as viewed,\n SUM(step_2_start) as started_signup,\n SUM(step_3_complete) as completed_signup,\n SUM(step_4_purchase) as purchased,\n ROUND(100.0 * SUM(step_2_start) / NULLIF(SUM(step_1_view), 0), 1) as view_to_start_pct,\n ROUND(100.0 * SUM(step_3_complete) / NULLIF(SUM(step_2_start), 0), 1) as start_to_complete_pct,\n ROUND(100.0 * SUM(step_4_purchase) / NULLIF(SUM(step_3_complete), 0), 1) as complete_to_purchase_pct\nFROM funnel;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Deduplication","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Keep the most recent record per key\nWITH ranked AS (\n SELECT\n *,\n ROW_NUMBER() OVER (\n PARTITION BY entity_id\n ORDER BY updated_at DESC\n ) as rn\n FROM source_table\n)\nSELECT * FROM ranked WHERE rn = 1;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Error Handling and Debugging","type":"text"}]},{"type":"paragraph","content":[{"text":"When a query fails:","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Syntax errors","type":"text","marks":[{"type":"strong"}]},{"text":": Check for dialect-specific syntax (e.g., ","type":"text"},{"text":"ILIKE","type":"text","marks":[{"type":"code_inline"}]},{"text":" not available in BigQuery, ","type":"text"},{"text":"SAFE_DIVIDE","type":"text","marks":[{"type":"code_inline"}]},{"text":" only in BigQuery)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Column not found","type":"text","marks":[{"type":"strong"}]},{"text":": Verify column names against schema -- check for typos, case sensitivity (PostgreSQL is case-sensitive for quoted identifiers)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Type mismatches","type":"text","marks":[{"type":"strong"}]},{"text":": Cast explicitly when comparing different types (","type":"text"},{"text":"CAST(col AS DATE)","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"col::DATE","type":"text","marks":[{"type":"code_inline"}]},{"text":")","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Division by zero","type":"text","marks":[{"type":"strong"}]},{"text":": Use ","type":"text"},{"text":"NULLIF(denominator, 0)","type":"text","marks":[{"type":"code_inline"}]},{"text":" or dialect-specific safe division","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ambiguous columns","type":"text","marks":[{"type":"strong"}]},{"text":": Always qualify column names with table alias in JOINs","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Group by errors","type":"text","marks":[{"type":"strong"}]},{"text":": All non-aggregated columns must be in GROUP BY (except in BigQuery which allows grouping by alias)","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"sql-queries","author":"@skillopedia","source":{"stars":18616,"repo_name":"knowledge-work-plugins","origin_url":"https://github.com/anthropics/knowledge-work-plugins/blob/HEAD/data/skills/sql-queries/SKILL.md","repo_owner":"anthropics","body_sha256":"80a84c01d333d9f678000b89e5c60e3a30cd4554c21fec5806cbed8f6369aa16","cluster_key":"a0d3ab53103f0b958b9da9dadaecbdab33bb1854eef1637d750fd5eedc03b326","clean_bundle":{"format":"clean-skill-bundle-v1","source":"anthropics/knowledge-work-plugins/data/skills/sql-queries/SKILL.md","bundle_sha256":"cc221fe3d3c077d565efab2025c7e5ff0600187bd2631be48d6300d8423b9865","attachment_count":0,"text_attachments":0,"binary_attachments":0},"cluster_size":1,"skill_md_path":"data/skills/sql-queries/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"web-development","category_label":"Web"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"web-development","import_tag":"clean-skills-v1","description":"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.","user-invocable":false}},"renderedAt":1782979318654}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.