sql-queries

Name: sql-queries
Author: anthropics

3.3kinstalls2.7kforks

How to install sql-queries?

anthropics/knowledge-work-plugins/sql-queries

$npx -y skills add anthropics/knowledge-work-plugins --skill sql-queries

Installs into the current project.

Summary

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 aggregat

Files · 1

View on GitHub

SKILL.md

1	# SQL Queries Skill
2
3	Write correct, performant, readable SQL across all major data warehouse dialects.
4
5	## Dialect-Specific Reference
6
7	### PostgreSQL (including Aurora, RDS, Supabase, Neon)
8
9	Date/time:
10	```sql
11	-- Current date/time
12	CURRENT_DATE, CURRENT_TIMESTAMP, NOW()
13
14	-- Date arithmetic
15	date_column + INTERVAL '7 days'
16	date_column - INTERVAL '1 month'
17
18	-- Truncate to period
19	DATE_TRUNC('month', created_at)
20
21	-- Extract parts
22	EXTRACT(YEAR FROM created_at)
23	EXTRACT(DOW FROM created_at) -- 0=Sunday
24
25	-- Format
26	TO_CHAR(created_at, 'YYYY-MM-DD')
27	```
28
29	String functions:
30	```sql
31	-- Concatenation
32	first_name \|\| ' ' \|\| last_name
33	CONCAT(first_name, ' ', last_name)
34
35	-- Pattern matching
36	column ILIKE '%pattern%' -- case-insensitive
37	column ~ '^regex_pattern$' -- regex
38
39	-- String manipulation
40	LEFT(str, n), RIGHT(str, n)
41	SPLIT_PART(str, delimiter, position)
42	REGEXP_REPLACE(str, pattern, replacement)
43	```
44
45	Arrays and JSON:
46	```sql
47	-- JSON access
48	data->>'key' -- text
49	data->'nested'->'key' -- json
50	data#>>'{path,to,key}' -- nested text
51
52	-- Array operations
53	ARRAY_AGG(column)
54	ANY(array_column)
55	array_column @> ARRAY['value']
56	```
57
58	Performance tips:
59	- Use `EXPLAIN ANALYZE` to profile queries
60	- Create indexes on frequently filtered/joined columns
61	- Use `EXISTS` over `IN` for correlated subqueries
62	- Partial indexes for common filter conditions
63	- Use connection pooling for concurrent access
64
65	---
66
67	### Snowflake
68
69	Date/time:
70	```sql
71	-- Current date/time
72	CURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()
73
74	-- Date arithmetic
75	DATEADD(day, 7, date_column)
76	DATEDIFF(day, start_date, end_date)
77
78	-- Truncate to period
79	DATE_TRUNC('month', created_at)
80
81	-- Extract parts
82	YEAR(created_at), MONTH(created_at), DAY(created_at)
83	DAYOFWEEK(created_at)
84
85	-- Format
86	TO_CHAR(created_at, 'YYYY-MM-DD')
87	```
88
89	String functions:
90	```sql
91	-- Case-insensitive by default (depends on collation)
92	column ILIKE '%pattern%'
93	REGEXP_LIKE(column, 'pattern')
94
95	-- Parse JSON
96	column:key::string -- dot notation for VARIANT
97	PARSE_JSON('{"key": "value"}')
98	GET_PATH(variant_col, 'path.to.key')
99
100	-- Flatten arrays/objects
101	SELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f
102	```
103
104	Semi-structured data:
105	```sql
106	-- VARIANT type access
107	data:customer:name::STRING
108	data:items[0]:price::NUMBER
109
110	-- Flatten nested structures
111	SELECT
112	t.id,
113	item.value:name::STRING as item_name,
114	item.value:qty::NUMBER as quantity
115	FROM my_table t,
116	LATERAL FLATTEN(input => t.data:items) item
117	```
118
119	Performance tips:
120	- Use clustering keys on large tables (not traditional indexes)
121	- Filter on clustering key columns for partition pruning
122	- Set appropriate warehouse size for query complexity
123	- Use `RESULT_SCAN(LAST_QUERY_ID())` to avoid re-running expensive queries
124	- Use transient tables for staging/temp data
125
126	---
127
128	### BigQuery (Google Cloud)
129
130	Date/time:
131	```sql
132	-- Current date/time
133	CURRENT_DATE(), CURRENT_TIMESTAMP()
134
135	-- Date arithmetic
136	DATE_ADD(date_column, INTERVAL 7 DAY)
137	DATE_SUB(date_column, INTERVAL 1 MONTH)
138	DATE_DIFF(end_date, start_date, DAY)
139	TIMESTAMP_DIFF(end_ts, start_ts, HOUR)
140
141	-- Truncate to period
142	DATE_TRUNC(created_at, MONTH)
143	TIMESTAMP_TRUNC(created_at, HOUR)
144
145	-- Extract parts
146	EXTRACT(YEAR FROM created_at)
147	EXTRACT(DAYOFWEEK FROM created_at) -- 1=Sunday
148
149	-- Format
150	FORMAT_DATE('%Y-%m-%d', date_column)
151	FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts_column)
152	```
153
154	String functions:
155	```sql
156	-- No ILIKE, use LOWER()
157	LOWER(column) LIKE '%pattern%'
158	REGEXP_CONTAINS(column, r'pattern')
159	REGEXP_EXTRACT(column, r'pattern')
160
161	-- String manipulation
162	SPLIT(str, delimiter) -- returns ARRAY
163	ARRAY_TO_STRING(array, delimiter)
164	```
165
166	Arrays and structs:
167	```sql
168	-- Array operations
169	ARRAY_AGG(column)
170	UNNEST(array_column)
171	ARRAY_LENGTH(array_column)
172	value IN UNNEST(array_column)
173
174	-- Struct access
175	struct_column.field_name
176	```
177
178	Performance tips:
179	- Always filter on partition columns (usually date) to reduce bytes scanned
180	- Use clustering for frequently filtered columns within partitions
181	- Use `APPROX_COUNT_DISTINCT()` for large-scale cardinality estimates
182	- Avoid `SELECT *` -- billing is per-byte scanned
183	- Use `DECLARE` and `SET` for parameterized scripts
184	- Preview query cost with dry run before executing large queries
185
186	---
187
188	### Redshift (Amazon)
189
190	Date/time:
191	```sql
192	-- Current date/time
193	CURRENT_DATE, GETDATE(), SYSDATE
194
195	-- Date arithmetic
196	DATEADD(day, 7, date_column)
197	DATEDIFF(day, start_date, end_date)
198
199	-- Truncate to period
200	DATE_TRUNC('month', created_at)
201
202	-- Extract parts
203	EXTRACT(YEAR FROM created_at)
204	DATE_PART('dow', created_at)
205	```
206
207	String functions:
208	```sql
209	-- Case-insensitive
210	column ILIKE '%pattern%'
211	REGEXP_INSTR(column, 'pattern') > 0
212
213	-- S

People who liked this also viewed

6 picks

Type

sql-queries

3.3kinstalls2.7kforks

View on GitHub

How to install sql-queries?

anthropics/knowledge-work-plugins/sql-queries

$npx -y skills add anthropics/knowledge-work-plugins --skill sql-queries

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# SQL Queries Skill
2
3	Write correct, performant, readable SQL across all major data warehouse dialects.
4
5	## Dialect-Specific Reference
6
7	### PostgreSQL (including Aurora, RDS, Supabase, Neon)
8
9	Date/time:
10	```sql
11	-- Current date/time
12	CURRENT_DATE, CURRENT_TIMESTAMP, NOW()
13
14	-- Date arithmetic
15	date_column + INTERVAL '7 days'
16	date_column - INTERVAL '1 month'
17
18	-- Truncate to period
19	DATE_TRUNC('month', created_at)
20
21	-- Extract parts
22	EXTRACT(YEAR FROM created_at)
23	EXTRACT(DOW FROM created_at) -- 0=Sunday
24
25	-- Format
26	TO_CHAR(created_at, 'YYYY-MM-DD')
27	```
28
29	String functions:
30	```sql
31	-- Concatenation
32	first_name \|\| ' ' \|\| last_name
33	CONCAT(first_name, ' ', last_name)
34
35	-- Pattern matching
36	column ILIKE '%pattern%' -- case-insensitive
37	column ~ '^regex_pattern$' -- regex
38
39	-- String manipulation
40	LEFT(str, n), RIGHT(str, n)
41	SPLIT_PART(str, delimiter, position)
42	REGEXP_REPLACE(str, pattern, replacement)
43	```
44
45	Arrays and JSON:
46	```sql
47	-- JSON access
48	data->>'key' -- text
49	data->'nested'->'key' -- json
50	data#>>'{path,to,key}' -- nested text
51
52	-- Array operations
53	ARRAY_AGG(column)
54	ANY(array_column)
55	array_column @> ARRAY['value']
56	```
57
58	Performance tips:
59	- Use `EXPLAIN ANALYZE` to profile queries
60	- Create indexes on frequently filtered/joined columns
61	- Use `EXISTS` over `IN` for correlated subqueries
62	- Partial indexes for common filter conditions
63	- Use connection pooling for concurrent access
64
65	---
66
67	### Snowflake
68
69	Date/time:
70	```sql
71	-- Current date/time
72	CURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE()
73
74	-- Date arithmetic
75	DATEADD(day, 7, date_column)
76	DATEDIFF(day, start_date, end_date)
77
78	-- Truncate to period
79	DATE_TRUNC('month', created_at)
80
81	-- Extract parts
82	YEAR(created_at), MONTH(created_at), DAY(created_at)
83	DAYOFWEEK(created_at)
84
85	-- Format
86	TO_CHAR(created_at, 'YYYY-MM-DD')
87	```
88
89	String functions:
90	```sql
91	-- Case-insensitive by default (depends on collation)
92	column ILIKE '%pattern%'
93	REGEXP_LIKE(column, 'pattern')
94
95	-- Parse JSON
96	column:key::string -- dot notation for VARIANT
97	PARSE_JSON('{"key": "value"}')
98	GET_PATH(variant_col, 'path.to.key')
99
100	-- Flatten arrays/objects
101	SELECT f.value FROM table, LATERAL FLATTEN(input => array_col) f
102	```
103
104	Semi-structured data:
105	```sql
106	-- VARIANT type access
107	data:customer:name::STRING
108	data:items[0]:price::NUMBER
109
110	-- Flatten nested structures
111	SELECT
112	t.id,
113	item.value:name::STRING as item_name,
114	item.value:qty::NUMBER as quantity
115	FROM my_table t,
116	LATERAL FLATTEN(input => t.data:items) item
117	```
118
119	Performance tips:
120	- Use clustering keys on large tables (not traditional indexes)
121	- Filter on clustering key columns for partition pruning
122	- Set appropriate warehouse size for query complexity
123	- Use `RESULT_SCAN(LAST_QUERY_ID())` to avoid re-running expensive queries
124	- Use transient tables for staging/temp data
125
126	---
127
128	### BigQuery (Google Cloud)
129
130	Date/time:
131	```sql
132	-- Current date/time
133	CURRENT_DATE(), CURRENT_TIMESTAMP()
134
135	-- Date arithmetic
136	DATE_ADD(date_column, INTERVAL 7 DAY)
137	DATE_SUB(date_column, INTERVAL 1 MONTH)
138	DATE_DIFF(end_date, start_date, DAY)
139	TIMESTAMP_DIFF(end_ts, start_ts, HOUR)
140
141	-- Truncate to period
142	DATE_TRUNC(created_at, MONTH)
143	TIMESTAMP_TRUNC(created_at, HOUR)
144
145	-- Extract parts
146	EXTRACT(YEAR FROM created_at)
147	EXTRACT(DAYOFWEEK FROM created_at) -- 1=Sunday
148
149	-- Format
150	FORMAT_DATE('%Y-%m-%d', date_column)
151	FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts_column)
152	```
153
154	String functions:
155	```sql
156	-- No ILIKE, use LOWER()
157	LOWER(column) LIKE '%pattern%'
158	REGEXP_CONTAINS(column, r'pattern')
159	REGEXP_EXTRACT(column, r'pattern')
160
161	-- String manipulation
162	SPLIT(str, delimiter) -- returns ARRAY
163	ARRAY_TO_STRING(array, delimiter)
164	```
165
166	Arrays and structs:
167	```sql
168	-- Array operations
169	ARRAY_AGG(column)
170	UNNEST(array_column)
171	ARRAY_LENGTH(array_column)
172	value IN UNNEST(array_column)
173
174	-- Struct access
175	struct_column.field_name
176	```
177
178	Performance tips:
179	- Always filter on partition columns (usually date) to reduce bytes scanned
180	- Use clustering for frequently filtered columns within partitions
181	- Use `APPROX_COUNT_DISTINCT()` for large-scale cardinality estimates
182	- Avoid `SELECT *` -- billing is per-byte scanned
183	- Use `DECLARE` and `SET` for parameterized scripts
184	- Preview query cost with dry run before executing large queries
185
186	---
187
188	### Redshift (Amazon)
189
190	Date/time:
191	```sql
192	-- Current date/time
193	CURRENT_DATE, GETDATE(), SYSDATE
194
195	-- Date arithmetic
196	DATEADD(day, 7, date_column)
197	DATEDIFF(day, start_date, end_date)
198
199	-- Truncate to period
200	DATE_TRUNC('month', created_at)
201
202	-- Extract parts
203	EXTRACT(YEAR FROM created_at)
204	DATE_PART('dow', created_at)
205	```
206
207	String functions:
208	```sql
209	-- Case-insensitive
210	column ILIKE '%pattern%'
211	REGEXP_INSTR(column, 'pattern') > 0
212
213	-- S

People who liked this also viewed

6 picks

Type