Masters modern SQL across PostgreSQL, BigQuery, Snowflake, and hybrid OLTP/OLAP systems — covering advanced query techniques, dimensional modeling, time-series SQL, and data warehouse patterns. Use when writing complex analytics SQL, designing cloud database schemas, or optimizing cross-platform SQL workloads.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Masters modern SQL across PostgreSQL, BigQuery, Snowflake, and hybrid OLTP/OLAP systems — covering advanced query techniques, dimensional modeling, time-series SQL, and data warehouse patterns. Use when writing complex analytics SQL, designing cloud database schemas, or optimizing cross-platform SQL workloads.
-- Running total + rank within partitionSELECT
user_id,
order_date,
total,
SUM(total) OVER (PARTITIONBY user_id ORDERBY order_date) AS running_total,
RANK() OVER (PARTITIONBY user_id ORDERBY total DESC) AS rank_in_user
FROM orders;
Recursive CTE (Hierarchical Data)
WITHRECURSIVE org_tree AS (
-- Base case: root nodesSELECT id, name, parent_id, 0AS depth
FROM employees WHERE parent_id ISNULLUNIONALL-- Recursive caseSELECT e.id, e.name, e.parent_id, ot.depth +1FROM employees e
JOIN org_tree ot ON e.parent_id = ot.id
)
SELECT*FROM org_tree ORDERBY depth, name;
HTAP Pattern — Separate Read/Write Paths
-- Write path: OLTP primary (PostgreSQL)INSERT INTO orders (user_id, total, created_at) VALUES ($1, $2, NOW());
-- Read path: replica or analytics DB-- Use logical replication to BigQuery/Snowflake for heavy aggregationsSELECT DATE_TRUNC('month', created_at), SUM(total)
FROM orders
GROUPBY1ORDERBY1;
-- Route this to read replica, not primary
SCD Type 2 (Slowly Changing Dimensions)
-- Invalidate current record, insert new versionUPDATE dim_customers
SET valid_to = NOW(), is_current =FALSEWHERE customer_id = $1AND is_current =TRUE;
INSERT INTO dim_customers (customer_id, name, email, valid_from, valid_to, is_current)
VALUES ($1, $2, $3, NOW(), '9999-12-31', TRUE);
Anti-Patterns
Running heavy OLAP aggregations on OLTP primary — use read replica or separate analytics DB
SELECT * on wide fact tables in analytics workloads — columns are stored separately in columnar DBs
Non-partitioned tables for time-series data exceeding 1M rows — always partition by time
Correlated subqueries in analytical queries — always transform to JOINs or window functions
Implicit type casting in JOIN conditions — prevents index usage, causes full scans
DDL inside transactions on BigQuery/Snowflake — not supported; manage schema changes separately
Documentation Sources
PostgreSQL docs: Query MCP context7 with library ID /postgresql/postgresql
BigQuery: Query MCP context7 with library ID /googleapis/google-cloud-bigquery
Snowflake SQL reference: WebFetch from Snowflake documentation
TimescaleDB: Query MCP context7
Reference Files
(None yet — patterns are inline above. Add reference/cloud-platform-sql.md when cloud-specific patterns grow beyond this file.)
