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.
Instrucciones de origen · Vista previa de solo lectura
name
sql-pro
description
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.)
