SQL Pro Core Workflow 1. Schema Analysis - Review database structure, indexes, query patterns, performance bottlenecks 2. Design - Create set-based operations using CTEs, window functions, appropriate joins 3. Optimize - Analyze execution plans, implement covering indexes, eliminate table scans 4. Verify - Run and confirm no sequential scans on large tables; if query does not meet sub-100ms target, iterate on index selection or query rewrite before proceeding 5. Document - Provide query explanations, index rationale, performance metrics Reference Guide Load detailed guidance based on context:…

),\n CONSTRAINT chk_hire_after_birth CHECK (hire_date > birth_date + INTERVAL '16 years'),\n CONSTRAINT chk_hire_not_future CHECK (hire_date \u003c= CURRENT_DATE)\n);\n\n-- Unique constraints (including composite)\nCREATE TABLE user_preferences (\n user_id INT NOT NULL,\n preference_key VARCHAR(50) NOT NULL,\n preference_value TEXT,\n\n CONSTRAINT uq_user_preference UNIQUE (user_id, preference_key)\n);\n\n-- NOT NULL constraints with defaults\nCREATE TABLE products (\n product_id SERIAL PRIMARY KEY,\n name VARCHAR(200) NOT NULL,\n description TEXT,\n price DECIMAL(10,2) NOT NULL DEFAULT 0.00,\n is_active BOOLEAN NOT NULL DEFAULT true,\n created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Exclusion constraints (PostgreSQL - prevent overlapping ranges)\nCREATE TABLE room_bookings (\n booking_id SERIAL PRIMARY KEY,\n room_id INT NOT NULL,\n booked_during TSTZRANGE NOT NULL,\n\n EXCLUDE USING GIST (\n room_id WITH =,\n booked_during WITH &&\n ) -- Prevent overlapping bookings for same room\n);\n```\n\n## Indexing Strategy\n\n```sql\n-- Index foreign keys (critical for JOIN performance)\nCREATE INDEX idx_orders_customer_id ON orders(customer_id);\nCREATE INDEX idx_order_items_order_id ON order_items(order_id);\nCREATE INDEX idx_order_items_product_id ON order_items(product_id);\n\n-- Composite index for common queries\nCREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);\n-- Supports:\n-- WHERE customer_id = ? AND order_date > ?\n-- WHERE customer_id = ? ORDER BY order_date DESC\n\n-- Partial index for common filters\nCREATE INDEX idx_active_products ON products(category, price)\nWHERE is_active = true AND deleted_at IS NULL;\n\n-- Unique index for business rules\nCREATE UNIQUE INDEX idx_users_active_email ON users(LOWER(email))\nWHERE deleted_at IS NULL;\n-- Ensures no duplicate emails among active users\n```\n\n## Common Design Patterns\n\n```sql\n-- Polymorphic associations (flexible but harder to enforce integrity)\nCREATE TABLE comments (\n comment_id SERIAL PRIMARY KEY,\n commentable_type VARCHAR(50) NOT NULL, -- 'Post', 'Photo', 'Video'\n commentable_id INT NOT NULL,\n content TEXT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n\n -- Cannot enforce FK without triggers/application logic\n CHECK (commentable_type IN ('Post', 'Photo', 'Video'))\n);\n\n-- Better: Separate tables with proper FKs\nCREATE TABLE post_comments (\n comment_id SERIAL PRIMARY KEY,\n post_id INT NOT NULL REFERENCES posts(post_id),\n content TEXT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE photo_comments (\n comment_id SERIAL PRIMARY KEY,\n photo_id INT NOT NULL REFERENCES photos(photo_id),\n content TEXT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Many-to-many with attributes (junction/bridge table)\nCREATE TABLE students (\n student_id SERIAL PRIMARY KEY,\n name VARCHAR(100) NOT NULL\n);\n\nCREATE TABLE courses (\n course_id SERIAL PRIMARY KEY,\n course_name VARCHAR(100) NOT NULL\n);\n\nCREATE TABLE enrollments (\n enrollment_id SERIAL PRIMARY KEY,\n student_id INT NOT NULL REFERENCES students(student_id),\n course_id INT NOT NULL REFERENCES courses(course_id),\n enrollment_date DATE NOT NULL DEFAULT CURRENT_DATE,\n grade CHAR(2),\n status VARCHAR(20) DEFAULT 'active',\n\n UNIQUE (student_id, course_id),\n CHECK (status IN ('active', 'completed', 'dropped'))\n);\n\n-- Self-referencing hierarchy\nCREATE TABLE categories (\n category_id SERIAL PRIMARY KEY,\n category_name VARCHAR(100) NOT NULL,\n parent_category_id INT REFERENCES categories(category_id),\n level INT NOT NULL DEFAULT 0,\n\n CHECK (category_id != parent_category_id) -- Prevent self-reference\n);\n\n-- Adjacency list example\nINSERT INTO categories VALUES\n (1, 'Electronics', NULL, 0),\n (2, 'Computers', 1, 1),\n (3, 'Laptops', 2, 2),\n (4, 'Desktops', 2, 2);\n```\n\n## Temporal/Historical Data\n\n```sql\n-- Slowly Changing Dimension Type 2 (SCD2) - Full history\nCREATE TABLE customer_history (\n customer_history_id SERIAL PRIMARY KEY,\n customer_id INT NOT NULL,\n name VARCHAR(100) NOT NULL,\n email VARCHAR(255) NOT NULL,\n address TEXT,\n valid_from TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n valid_to TIMESTAMP,\n is_current BOOLEAN NOT NULL DEFAULT true,\n\n CHECK (valid_to IS NULL OR valid_to > valid_from)\n);\n\n-- Ensure only one current record per customer\nCREATE UNIQUE INDEX idx_customer_current ON customer_history(customer_id)\nWHERE is_current = true;\n\n-- Temporal tables (PostgreSQL system-versioning)\nCREATE TABLE products (\n product_id SERIAL PRIMARY KEY,\n name VARCHAR(200) NOT NULL,\n price DECIMAL(10,2) NOT NULL,\n sys_period TSTZRANGE NOT NULL DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL)\n);\n\nCREATE TABLE products_history (LIKE products);\n\nCREATE TRIGGER versioning_trigger\nBEFORE INSERT OR UPDATE OR DELETE ON products\nFOR EACH ROW EXECUTE FUNCTION versioning('sys_period', 'products_history', true);\n```\n\n## Soft Deletes\n\n```sql\n-- Soft delete pattern\nCREATE TABLE posts (\n post_id SERIAL PRIMARY KEY,\n title VARCHAR(255) NOT NULL,\n content TEXT NOT NULL,\n author_id INT NOT NULL REFERENCES users(user_id),\n created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n deleted_at TIMESTAMP, -- NULL = active, non-NULL = deleted\n deleted_by INT REFERENCES users(user_id)\n);\n\n-- Index for filtering active records\nCREATE INDEX idx_posts_active ON posts(created_at DESC)\nWHERE deleted_at IS NULL;\n\n-- View for active posts only\nCREATE VIEW active_posts AS\nSELECT post_id, title, content, author_id, created_at, updated_at\nFROM posts\nWHERE deleted_at IS NULL;\n```\n\n## Audit Trails\n\n```sql\n-- Audit table pattern\nCREATE TABLE audit_log (\n audit_id BIGSERIAL PRIMARY KEY,\n table_name VARCHAR(100) NOT NULL,\n record_id BIGINT NOT NULL,\n action VARCHAR(10) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),\n old_values JSONB,\n new_values JSONB,\n changed_by INT REFERENCES users(user_id),\n changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);\nCREATE INDEX idx_audit_timestamp ON audit_log(changed_at DESC);\n\n-- Trigger function for automatic auditing\nCREATE OR REPLACE FUNCTION audit_trigger_func()\nRETURNS TRIGGER AS $\nBEGIN\n IF (TG_OP = 'DELETE') THEN\n INSERT INTO audit_log (table_name, record_id, action, old_values)\n VALUES (TG_TABLE_NAME, OLD.product_id, 'DELETE', row_to_json(OLD));\n RETURN OLD;\n ELSIF (TG_OP = 'UPDATE') THEN\n INSERT INTO audit_log (table_name, record_id, action, old_values, new_values)\n VALUES (TG_TABLE_NAME, NEW.product_id, 'UPDATE', row_to_json(OLD), row_to_json(NEW));\n RETURN NEW;\n ELSIF (TG_OP = 'INSERT') THEN\n INSERT INTO audit_log (table_name, record_id, action, new_values)\n VALUES (TG_TABLE_NAME, NEW.product_id, 'INSERT', row_to_json(NEW));\n RETURN NEW;\n END IF;\nEND;\n$ LANGUAGE plpgsql;\n\nCREATE TRIGGER products_audit\nAFTER INSERT OR UPDATE OR DELETE ON products\nFOR EACH ROW EXECUTE FUNCTION audit_trigger_func();\n```\n\n## Schema Design Best Practices\n\n1. **Choose appropriate data types**: Use smallest type that fits (INT vs BIGINT, VARCHAR(50) vs TEXT)\n2. **Index foreign keys**: Always index FK columns for JOIN performance\n3. **Avoid NULLs when possible**: Use NOT NULL with defaults\n4. **Use constraints**: Enforce data integrity at database level\n5. **Normalize to 3NF**: Then denormalize strategically for performance\n6. **Consider soft deletes**: For auditing and data recovery\n7. **Plan for growth**: Use BIGINT for high-volume PKs\n8. **Document schema**: Comment tables and complex constraints\n9. **Version control**: Track schema changes with migrations\n10. **Test with realistic data**: Validate design with production-scale data\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":12540,"content_sha256":"b8d0676ac91213999c28e03dae2a7685d78dda05b6abe3f6b34690d2ae19d0f6"},{"filename":"references/dialect-differences.md","content":"# Database Dialect Differences\n\n## Auto-Incrementing Primary Keys\n\n```sql\n-- PostgreSQL\nCREATE TABLE users (\n user_id SERIAL PRIMARY KEY, -- or BIGSERIAL for BIGINT\n name VARCHAR(100)\n);\n-- Alternative (PostgreSQL 10+)\nCREATE TABLE users (\n user_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n name VARCHAR(100)\n);\n\n-- MySQL\nCREATE TABLE users (\n user_id INT AUTO_INCREMENT PRIMARY KEY,\n name VARCHAR(100)\n);\n\n-- SQL Server\nCREATE TABLE users (\n user_id INT IDENTITY(1,1) PRIMARY KEY,\n name VARCHAR(100)\n);\n\n-- Oracle\nCREATE TABLE users (\n user_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n name VARCHAR2(100)\n);\n-- Or using sequence (older approach)\nCREATE SEQUENCE user_id_seq;\nCREATE TABLE users (\n user_id NUMBER DEFAULT user_id_seq.NEXTVAL PRIMARY KEY,\n name VARCHAR2(100)\n);\n```\n\n## String Concatenation\n\n```sql\n-- PostgreSQL (strict - automatic casting)\nSELECT first_name || ' ' || last_name AS full_name FROM users;\nSELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; -- NULL-safe\n\n-- MySQL (automatic type conversion)\nSELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;\nSELECT first_name + ' ' + last_name FROM users; -- ERROR in MySQL\n\n-- SQL Server\nSELECT first_name + ' ' + last_name AS full_name FROM users;\nSELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; -- 2012+\n\n-- Oracle\nSELECT first_name || ' ' || last_name AS full_name FROM users;\nSELECT CONCAT(first_name, last_name) FROM users; -- Only 2 arguments!\n```\n\n## Date/Time Functions\n\n```sql\n-- Current timestamp\n-- PostgreSQL\nSELECT CURRENT_TIMESTAMP, NOW(), CURRENT_DATE, CURRENT_TIME;\n\n-- MySQL\nSELECT CURRENT_TIMESTAMP, NOW(), CURDATE(), CURTIME();\n\n-- SQL Server\nSELECT GETDATE(), SYSDATETIME(), CAST(GETDATE() AS DATE);\n\n-- Oracle\nSELECT SYSDATE, SYSTIMESTAMP, TRUNC(SYSDATE) FROM DUAL;\n\n-- Date arithmetic\n-- PostgreSQL\nSELECT order_date + INTERVAL '7 days' FROM orders;\nSELECT order_date - INTERVAL '1 month' FROM orders;\nSELECT AGE(CURRENT_DATE, birth_date) FROM users; -- Interval type\n\n-- MySQL\nSELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders;\nSELECT DATE_SUB(order_date, INTERVAL 1 MONTH) FROM orders;\nSELECT DATEDIFF(CURRENT_DATE, birth_date) FROM users; -- Days only\n\n-- SQL Server\nSELECT DATEADD(day, 7, order_date) FROM orders;\nSELECT DATEADD(month, -1, order_date) FROM orders;\nSELECT DATEDIFF(year, birth_date, GETDATE()) FROM users;\n\n-- Oracle\nSELECT order_date + 7 FROM orders; -- +7 days\nSELECT ADD_MONTHS(order_date, -1) FROM orders;\nSELECT MONTHS_BETWEEN(SYSDATE, birth_date) / 12 FROM users;\n\n-- Date formatting\n-- PostgreSQL\nSELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders;\n\n-- MySQL\nSELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;\n\n-- SQL Server\nSELECT FORMAT(order_date, 'yyyy-MM-dd') FROM orders;\nSELECT CONVERT(VARCHAR(10), order_date, 120) FROM orders; -- Style 120 = yyyy-MM-dd\n\n-- Oracle\nSELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders;\n```\n\n## LIMIT/OFFSET (Pagination)\n\n```sql\n-- PostgreSQL & MySQL\nSELECT * FROM products\nORDER BY product_id\nLIMIT 10 OFFSET 20;\n\n-- SQL Server (2012+)\nSELECT * FROM products\nORDER BY product_id\nOFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;\n\n-- SQL Server (older - ROW_NUMBER)\nSELECT * FROM (\n SELECT *, ROW_NUMBER() OVER (ORDER BY product_id) as rn\n FROM products\n) x\nWHERE rn BETWEEN 21 AND 30;\n\n-- Oracle (12c+)\nSELECT * FROM products\nORDER BY product_id\nOFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;\n\n-- Oracle (older - ROWNUM)\nSELECT * FROM (\n SELECT a.*, ROWNUM rnum FROM (\n SELECT * FROM products ORDER BY product_id\n ) a\n WHERE ROWNUM \u003c= 30\n)\nWHERE rnum > 20;\n```\n\n## Boolean Data Type\n\n```sql\n-- PostgreSQL (native BOOLEAN)\nCREATE TABLE users (\n user_id SERIAL PRIMARY KEY,\n is_active BOOLEAN DEFAULT true\n);\nSELECT * FROM users WHERE is_active = true;\n\n-- MySQL (TINYINT(1) or BOOLEAN alias)\nCREATE TABLE users (\n user_id INT AUTO_INCREMENT PRIMARY KEY,\n is_active BOOLEAN DEFAULT 1 -- Stored as TINYINT(1)\n);\nSELECT * FROM users WHERE is_active = 1;\n\n-- SQL Server (BIT)\nCREATE TABLE users (\n user_id INT IDENTITY(1,1) PRIMARY KEY,\n is_active BIT DEFAULT 1\n);\nSELECT * FROM users WHERE is_active = 1;\n\n-- Oracle (no native boolean in tables, use NUMBER or CHAR)\nCREATE TABLE users (\n user_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n is_active NUMBER(1) DEFAULT 1 CHECK (is_active IN (0, 1))\n);\nSELECT * FROM users WHERE is_active = 1;\n```\n\n## JSON/JSONB Support\n\n```sql\n-- PostgreSQL (JSONB - binary, indexable)\nCREATE TABLE events (\n event_id SERIAL PRIMARY KEY,\n event_data JSONB NOT NULL\n);\n\nINSERT INTO events (event_data) VALUES ('{\"user_id\": 123, \"action\": \"login\"}');\n\nSELECT event_data->>'user_id' as user_id FROM events;\nSELECT * FROM events WHERE event_data @> '{\"action\": \"login\"}';\nSELECT * FROM events WHERE event_data->>'user_id' = '123';\n\nCREATE INDEX idx_events_data ON events USING GIN (event_data);\n\n-- MySQL (8.0+)\nCREATE TABLE events (\n event_id INT AUTO_INCREMENT PRIMARY KEY,\n event_data JSON NOT NULL\n);\n\nSELECT JSON_EXTRACT(event_data, '$.user_id') as user_id FROM events;\nSELECT * FROM events WHERE JSON_EXTRACT(event_data, '$.action') = 'login';\n\nCREATE INDEX idx_events_user ON events ((CAST(event_data->>'$.user_id' AS UNSIGNED)));\n\n-- SQL Server (2016+)\nCREATE TABLE events (\n event_id INT IDENTITY(1,1) PRIMARY KEY,\n event_data NVARCHAR(MAX) CHECK (ISJSON(event_data) = 1)\n);\n\nSELECT JSON_VALUE(event_data, '$.user_id') as user_id FROM events;\nSELECT * FROM events WHERE JSON_VALUE(event_data, '$.action') = 'login';\n\n-- Oracle (12c+)\nCREATE TABLE events (\n event_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n event_data CLOB CHECK (event_data IS JSON)\n);\n\nSELECT JSON_VALUE(event_data, '$.user_id') as user_id FROM events;\nSELECT * FROM events WHERE JSON_EXISTS(event_data, '$.action?(@ == \"login\")');\n```\n\n## String Comparison (Case Sensitivity)\n\n```sql\n-- PostgreSQL (case-sensitive by default)\nSELECT * FROM users WHERE email = '[email protected]'; -- Won't match '[email protected]'\nSELECT * FROM users WHERE LOWER(email) = LOWER('[email protected]');\nSELECT * FROM users WHERE email ILIKE '[email protected]'; -- Case-insensitive\n\n-- MySQL (case-insensitive by default with utf8_general_ci collation)\nSELECT * FROM users WHERE email = '[email protected]'; -- Matches '[email protected]'\nSELECT * FROM users WHERE email COLLATE utf8_bin = '[email protected]'; -- Case-sensitive\n\n-- SQL Server (depends on collation, usually case-insensitive)\nSELECT * FROM users WHERE email = '[email protected]'; -- Usually matches\nSELECT * FROM users WHERE email COLLATE Latin1_General_BIN = '[email protected]'; -- Case-sensitive\n\n-- Oracle (case-sensitive by default)\nSELECT * FROM users WHERE email = '[email protected]'; -- Won't match '[email protected]'\nSELECT * FROM users WHERE UPPER(email) = UPPER('[email protected]');\n```\n\n## Recursive CTEs\n\n```sql\n-- PostgreSQL\nWITH RECURSIVE subordinates AS (\n SELECT employee_id, name, manager_id, 1 as level\n FROM employees WHERE manager_id IS NULL\n UNION ALL\n SELECT e.employee_id, e.name, e.manager_id, s.level + 1\n FROM employees e\n INNER JOIN subordinates s ON e.manager_id = s.employee_id\n)\nSELECT * FROM subordinates;\n\n-- MySQL (8.0+) - Same syntax as PostgreSQL\nWITH RECURSIVE subordinates AS (\n SELECT employee_id, name, manager_id, 1 as level\n FROM employees WHERE manager_id IS NULL\n UNION ALL\n SELECT e.employee_id, e.name, e.manager_id, s.level + 1\n FROM employees e\n INNER JOIN subordinates s ON e.manager_id = s.employee_id\n)\nSELECT * FROM subordinates;\n\n-- SQL Server - No RECURSIVE keyword\nWITH subordinates AS (\n SELECT employee_id, name, manager_id, 1 as level\n FROM employees WHERE manager_id IS NULL\n UNION ALL\n SELECT e.employee_id, e.name, e.manager_id, s.level + 1\n FROM employees e\n INNER JOIN subordinates s ON e.manager_id = s.employee_id\n)\nSELECT * FROM subordinates;\n\n-- Oracle - CONNECT BY (traditional hierarchical queries)\nSELECT employee_id, name, manager_id, LEVEL\nFROM employees\nSTART WITH manager_id IS NULL\nCONNECT BY PRIOR employee_id = manager_id;\n```\n\n## Window Functions - Frame Specifications\n\n```sql\n-- PostgreSQL - Full support\nSELECT\n order_date,\n total,\n SUM(total) OVER (\n ORDER BY order_date\n RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW\n ) as rolling_7day\nFROM orders;\n\n-- MySQL (8.0+) - Limited RANGE support (no intervals)\nSELECT\n order_date,\n total,\n SUM(total) OVER (\n ORDER BY order_date\n ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n ) as rolling_7rows\nFROM orders;\n\n-- SQL Server - Full support\nSELECT\n order_date,\n total,\n SUM(total) OVER (\n ORDER BY order_date\n ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n ) as rolling_7rows\nFROM orders;\n\n-- Oracle - Full support\nSELECT\n order_date,\n total,\n SUM(total) OVER (\n ORDER BY order_date\n RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW\n ) as rolling_7day\nFROM orders;\n```\n\n## UPSERT (Insert or Update)\n\n```sql\n-- PostgreSQL (ON CONFLICT)\nINSERT INTO products (product_id, name, price)\nVALUES (123, 'Widget', 29.99)\nON CONFLICT (product_id)\nDO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;\n\n-- MySQL (ON DUPLICATE KEY)\nINSERT INTO products (product_id, name, price)\nVALUES (123, 'Widget', 29.99)\nON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price);\n\n-- MySQL 8.0.19+ (alternative)\nINSERT INTO products (product_id, name, price)\nVALUES (123, 'Widget', 29.99) AS new\nON DUPLICATE KEY UPDATE name = new.name, price = new.price;\n\n-- SQL Server (MERGE)\nMERGE INTO products AS target\nUSING (SELECT 123 AS product_id, 'Widget' AS name, 29.99 AS price) AS source\nON target.product_id = source.product_id\nWHEN MATCHED THEN\n UPDATE SET name = source.name, price = source.price\nWHEN NOT MATCHED THEN\n INSERT (product_id, name, price)\n VALUES (source.product_id, source.name, source.price);\n\n-- Oracle (MERGE)\nMERGE INTO products target\nUSING (SELECT 123 AS product_id, 'Widget' AS name, 29.99 AS price FROM DUAL) source\nON (target.product_id = source.product_id)\nWHEN MATCHED THEN\n UPDATE SET name = source.name, price = source.price\nWHEN NOT MATCHED THEN\n INSERT (product_id, name, price)\n VALUES (source.product_id, source.name, source.price);\n```\n\n## Data Type Mapping\n\n| Concept | PostgreSQL | MySQL | SQL Server | Oracle |\n|---------|-----------|-------|------------|--------|\n| Integer | INT, BIGINT | INT, BIGINT | INT, BIGINT | NUMBER(10), NUMBER(19) |\n| Decimal | NUMERIC, DECIMAL | DECIMAL | DECIMAL, NUMERIC | NUMBER(p,s) |\n| String | VARCHAR, TEXT | VARCHAR, TEXT | VARCHAR, NVARCHAR | VARCHAR2, CLOB |\n| Binary | BYTEA | BLOB, BINARY | VARBINARY, IMAGE | BLOB, RAW |\n| Boolean | BOOLEAN | BOOLEAN/TINYINT(1) | BIT | NUMBER(1) |\n| Date | DATE | DATE | DATE | DATE |\n| Timestamp | TIMESTAMP | DATETIME, TIMESTAMP | DATETIME, DATETIME2 | TIMESTAMP |\n| UUID | UUID | CHAR(36), BINARY(16) | UNIQUEIDENTIFIER | RAW(16) |\n| JSON | JSON, JSONB | JSON | NVARCHAR(MAX) | CLOB |\n| Array | ARRAY | JSON | Table variable | VARRAY, nested table |\n\n## Performance Tips by Database\n\n**PostgreSQL:**\n- Use EXPLAIN ANALYZE with BUFFERS\n- Leverage JSONB with GIN indexes\n- Use parallel query settings for large scans\n- Vacuum and analyze regularly\n- Consider table partitioning for 10M+ rows\n\n**MySQL:**\n- Choose InnoDB over MyISAM\n- Optimize buffer pool size\n- Use covering indexes aggressively\n- Be aware of case-insensitive defaults\n- Consider read replicas for scaling\n\n**SQL Server:**\n- Update statistics regularly\n- Use columnstore indexes for warehousing\n- Leverage query hints sparingly\n- Monitor execution plans\n- Use In-Memory OLTP for hot tables\n\n**Oracle:**\n- Use EXPLAIN PLAN\n- Leverage partitioning features\n- Use bind variables to avoid parsing\n- Configure SGA/PGA appropriately\n- Consider Real Application Clusters (RAC)\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":11951,"content_sha256":"5eac1a75d44e65dab3877353bf9e5963fa95dd990b5b1d853b17f744761f9586"},{"filename":"references/optimization.md","content":"# Query Optimization\n\n## EXPLAIN Plan Analysis\n\n```sql\n-- PostgreSQL EXPLAIN ANALYZE\nEXPLAIN (ANALYZE, BUFFERS, VERBOSE)\nSELECT\n c.customer_id,\n c.name,\n COUNT(o.order_id) as order_count,\n SUM(o.total) as lifetime_value\nFROM customers c\nLEFT JOIN orders o ON c.customer_id = o.customer_id\nWHERE c.created_at >= '2024-01-01'\nGROUP BY c.customer_id, c.name\nHAVING COUNT(o.order_id) > 5;\n\n/*\nKey metrics to analyze:\n- Planning Time: Time to generate plan\n- Execution Time: Actual runtime\n- Seq Scan: Table scans (bad for large tables)\n- Index Scan: Using indexes (good)\n- Rows: Estimated vs actual (large difference = stale stats)\n- Buffers: shared hit = cache, read = disk I/O\n- Loops: Nested loop iterations\n*/\n\n-- MySQL EXPLAIN\nEXPLAIN FORMAT=JSON\nSELECT * FROM orders o\nINNER JOIN customers c ON o.customer_id = c.customer_id\nWHERE o.order_date >= '2024-01-01'\n AND c.country = 'US';\n\n-- SQL Server execution plan\nSET STATISTICS IO ON;\nSET STATISTICS TIME ON;\n\nSELECT ...;\n\n-- Check actual vs estimated rows\nSELECT * FROM sys.dm_exec_query_stats;\n```\n\n## Index Design and Optimization\n\n```sql\n-- Covering index (all columns in index)\nCREATE INDEX idx_orders_covering ON orders (\n customer_id,\n order_date\n) INCLUDE (total, status);\n\n-- Query uses index-only scan (no table access needed)\nSELECT customer_id, order_date, total, status\nFROM orders\nWHERE customer_id = 123\n AND order_date >= '2024-01-01';\n\n-- Composite index (order matters!)\nCREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);\n-- Good: WHERE customer_id = X AND order_date > Y\n-- Good: WHERE customer_id = X\n-- Bad: WHERE order_date > Y (doesn't use index)\n\n-- Partial/Filtered index (smaller, faster)\nCREATE INDEX idx_active_orders ON orders (customer_id, order_date)\nWHERE status = 'active';\n\n-- Only used when query includes the filter\nSELECT * FROM orders\nWHERE customer_id = 123\n AND status = 'active'\n AND order_date >= '2024-01-01';\n\n-- Expression/Function-based index\nCREATE INDEX idx_users_lower_email ON users (LOWER(email));\n\n-- Now this uses the index\nSELECT * FROM users WHERE LOWER(email) = '[email protected]';\n\n-- GIN index for arrays/JSONB (PostgreSQL)\nCREATE INDEX idx_products_tags ON products USING GIN (tags);\nSELECT * FROM products WHERE tags @> ARRAY['electronics', 'sale'];\n\nCREATE INDEX idx_orders_metadata ON orders USING GIN (metadata jsonb_path_ops);\nSELECT * FROM orders WHERE metadata @> '{\"priority\": \"high\"}';\n```\n\n## Index Maintenance\n\n```sql\n-- PostgreSQL: Find missing indexes\nSELECT\n schemaname,\n tablename,\n seq_scan,\n seq_tup_read,\n idx_scan,\n seq_tup_read / seq_scan as avg_seq_read\nFROM pg_stat_user_tables\nWHERE seq_scan > 0\n AND seq_tup_read / seq_scan > 10000\nORDER BY seq_tup_read DESC;\n\n-- Find unused indexes\nSELECT\n schemaname,\n tablename,\n indexname,\n idx_scan,\n pg_size_pretty(pg_relation_size(indexrelid)) as index_size\nFROM pg_stat_user_indexes\nWHERE idx_scan = 0\n AND indexrelname NOT LIKE 'pg_toast%'\nORDER BY pg_relation_size(indexrelid) DESC;\n\n-- Find duplicate indexes\nSELECT\n pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) as size,\n (array_agg(idx))[1] as idx1,\n (array_agg(idx))[2] as idx2,\n (array_agg(idx))[3] as idx3\nFROM (\n SELECT\n indexrelid::regclass as idx,\n (indrelid::text ||E'\\n'|| indclass::text ||E'\\n'||\n indkey::text ||E'\\n'|| COALESCE(indexprs::text,'')||E'\\n'||\n COALESCE(indpred::text,'')) as key\n FROM pg_index\n) sub\nGROUP BY key\nHAVING COUNT(*) > 1\nORDER BY SUM(pg_relation_size(idx)) DESC;\n\n-- Reindex to reduce bloat\nREINDEX INDEX CONCURRENTLY idx_orders_customer_date;\n\n-- Update statistics\nANALYZE orders;\nANALYZE VERBOSE; -- Show progress\n```\n\n## Query Rewriting Patterns\n\n```sql\n-- Avoid SELECT DISTINCT when possible\n-- Bad: Forces sort/dedup\nSELECT DISTINCT customer_id FROM orders WHERE status = 'active';\n\n-- Good: Use EXISTS\nSELECT customer_id FROM customers c\nWHERE EXISTS (\n SELECT 1 FROM orders o\n WHERE o.customer_id = c.customer_id\n AND o.status = 'active'\n);\n\n-- Avoid NOT IN with NULLs\n-- Bad: NULL handling issues and poor performance\nSELECT * FROM customers\nWHERE customer_id NOT IN (SELECT customer_id FROM orders);\n\n-- Good: Use NOT EXISTS\nSELECT * FROM customers c\nWHERE NOT EXISTS (\n SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id\n);\n\n-- Push down filtering early\n-- Bad: Filter after JOIN\nSELECT c.*, o.*\nFROM customers c\nJOIN orders o ON c.customer_id = o.customer_id\nWHERE c.country = 'US' AND o.order_date >= '2024-01-01';\n\n-- Good: Use WHERE in subquery/CTE to reduce JOIN size\nWITH us_customers AS (\n SELECT customer_id, name\n FROM customers\n WHERE country = 'US'\n),\nrecent_orders AS (\n SELECT customer_id, order_id, total\n FROM orders\n WHERE order_date >= '2024-01-01'\n)\nSELECT c.*, o.*\nFROM us_customers c\nJOIN recent_orders o ON c.customer_id = o.customer_id;\n\n-- Avoid scalar subqueries in SELECT\n-- Bad: N+1 problem\nSELECT\n p.product_id,\n p.name,\n (SELECT COUNT(*) FROM reviews WHERE product_id = p.product_id) as review_count\nFROM products p;\n\n-- Good: Single JOIN with GROUP BY\nSELECT\n p.product_id,\n p.name,\n COUNT(r.review_id) as review_count\nFROM products p\nLEFT JOIN reviews r ON p.product_id = r.product_id\nGROUP BY p.product_id, p.name;\n```\n\n## Partitioning Strategies\n\n```sql\n-- Range partitioning by date (PostgreSQL)\nCREATE TABLE orders (\n order_id SERIAL,\n customer_id INT,\n order_date DATE NOT NULL,\n total DECIMAL(10,2)\n) PARTITION BY RANGE (order_date);\n\nCREATE TABLE orders_2024_q1 PARTITION OF orders\n FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');\n\nCREATE TABLE orders_2024_q2 PARTITION OF orders\n FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');\n\n-- Partition pruning in action\nEXPLAIN SELECT * FROM orders WHERE order_date >= '2024-02-01' AND order_date \u003c '2024-03-01';\n-- Only scans orders_2024_q1 partition\n\n-- List partitioning by category\nCREATE TABLE products (\n product_id SERIAL,\n category VARCHAR(50) NOT NULL,\n name VARCHAR(200)\n) PARTITION BY LIST (category);\n\nCREATE TABLE products_electronics PARTITION OF products\n FOR VALUES IN ('electronics', 'computers', 'phones');\n\nCREATE TABLE products_clothing PARTITION OF products\n FOR VALUES IN ('clothing', 'shoes', 'accessories');\n\n-- Hash partitioning for even distribution\nCREATE TABLE users (\n user_id SERIAL,\n email VARCHAR(255)\n) PARTITION BY HASH (user_id);\n\nCREATE TABLE users_p0 PARTITION OF users\n FOR VALUES WITH (MODULUS 4, REMAINDER 0);\nCREATE TABLE users_p1 PARTITION OF users\n FOR VALUES WITH (MODULUS 4, REMAINDER 1);\n```\n\n## Materialized Views\n\n```sql\n-- Create materialized view for expensive aggregations\nCREATE MATERIALIZED VIEW daily_sales_summary AS\nSELECT\n DATE_TRUNC('day', order_date) as day,\n COUNT(*) as order_count,\n SUM(total) as revenue,\n AVG(total) as avg_order_value,\n COUNT(DISTINCT customer_id) as unique_customers\nFROM orders\nGROUP BY DATE_TRUNC('day', order_date);\n\nCREATE UNIQUE INDEX idx_daily_sales_day ON daily_sales_summary (day);\n\n-- Refresh strategy\nREFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;\n\n-- Auto-refresh with trigger (PostgreSQL)\nCREATE OR REPLACE FUNCTION refresh_daily_sales()\nRETURNS TRIGGER AS $\nBEGIN\n REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;\n RETURN NULL;\nEND;\n$ LANGUAGE plpgsql;\n\nCREATE TRIGGER trigger_refresh_daily_sales\nAFTER INSERT OR UPDATE OR DELETE ON orders\nFOR EACH STATEMENT\nEXECUTE FUNCTION refresh_daily_sales();\n```\n\n## Query Hints and Optimization\n\n```sql\n-- PostgreSQL: Force index usage (use sparingly)\nSET enable_seqscan = OFF;\nSELECT /*+ IndexScan(orders idx_orders_customer) */ * FROM orders WHERE customer_id = 123;\nSET enable_seqscan = ON;\n\n-- SQL Server: Query hints\nSELECT * FROM orders WITH (INDEX(idx_orders_customer_date))\nWHERE customer_id = 123;\n\n-- Force specific join type\nSELECT * FROM customers c\nINNER MERGE JOIN orders o ON c.customer_id = o.customer_id;\n\n-- MySQL: Index hints\nSELECT * FROM orders USE INDEX (idx_orders_customer_date)\nWHERE customer_id = 123;\n\nSELECT * FROM orders FORCE INDEX (idx_orders_customer_date)\nWHERE customer_id = 123;\n\n-- PostgreSQL: Parallel query tuning\nSET max_parallel_workers_per_gather = 4;\nALTER TABLE large_table SET (parallel_workers = 4);\n```\n\n## Performance Monitoring Queries\n\n```sql\n-- PostgreSQL: Find slow queries\nSELECT\n query,\n calls,\n total_exec_time,\n mean_exec_time,\n max_exec_time,\n rows / calls as avg_rows\nFROM pg_stat_statements\nORDER BY mean_exec_time DESC\nLIMIT 20;\n\n-- Find blocking queries\nSELECT\n blocked_locks.pid AS blocked_pid,\n blocked_activity.usename AS blocked_user,\n blocking_locks.pid AS blocking_pid,\n blocking_activity.usename AS blocking_user,\n blocked_activity.query AS blocked_statement,\n blocking_activity.query AS blocking_statement\nFROM pg_catalog.pg_locks blocked_locks\nJOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid\nJOIN pg_catalog.pg_locks blocking_locks\n ON blocking_locks.locktype = blocked_locks.locktype\n AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database\n AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation\n AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page\n AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple\n AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid\n AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid\n AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid\n AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid\n AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid\n AND blocking_locks.pid != blocked_locks.pid\nJOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid\nWHERE NOT blocked_locks.granted;\n\n-- Table bloat detection\nSELECT\n schemaname,\n tablename,\n pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,\n n_dead_tup,\n n_live_tup,\n ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_pct\nFROM pg_stat_user_tables\nWHERE n_dead_tup > 1000\nORDER BY n_dead_tup DESC;\n```\n\n## Best Practices Checklist\n\n1. Always run EXPLAIN ANALYZE before optimizing\n2. Create indexes on foreign keys and WHERE/JOIN columns\n3. Use covering indexes for frequent queries\n4. Keep statistics up to date (ANALYZE regularly)\n5. Avoid SELECT *, specify needed columns\n6. Use EXISTS instead of IN for subqueries\n7. Filter early, aggregate late\n8. Consider partitioning for large tables (>10M rows)\n9. Use materialized views for expensive aggregations\n10. Monitor slow query log and pg_stat_statements\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":10820,"content_sha256":"dd197a02491cda9e1d26e2f0fdff4a5a90f4a54ccf4f6d57e5041a62c06be9a6"},{"filename":"references/query-patterns.md","content":"# Query Patterns\n\n## Common Table Expressions (CTEs)\n\n```sql\n-- Basic CTE for readability\nWITH active_users AS (\n SELECT user_id, username, created_at\n FROM users\n WHERE is_active = true\n AND last_login >= CURRENT_DATE - INTERVAL '30 days'\n),\nuser_orders AS (\n SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent\n FROM orders\n WHERE status = 'completed'\n GROUP BY user_id\n)\nSELECT\n u.username,\n u.created_at,\n COALESCE(o.order_count, 0) as orders,\n COALESCE(o.total_spent, 0) as lifetime_value\nFROM active_users u\nLEFT JOIN user_orders o ON u.user_id = o.user_id\nWHERE COALESCE(o.order_count, 0) > 0\nORDER BY o.total_spent DESC;\n\n-- CTE with multiple references (avoiding duplicate computation)\nWITH monthly_sales AS (\n SELECT\n DATE_TRUNC('month', sale_date) as month,\n product_id,\n SUM(quantity) as total_quantity,\n SUM(amount) as total_amount\n FROM sales\n WHERE sale_date >= '2024-01-01'\n GROUP BY DATE_TRUNC('month', sale_date), product_id\n)\nSELECT\n current.month,\n current.product_id,\n current.total_amount,\n current.total_amount - COALESCE(previous.total_amount, 0) as growth,\n ROUND(100.0 * (current.total_amount - COALESCE(previous.total_amount, 0))\n / NULLIF(previous.total_amount, 0), 2) as growth_pct\nFROM monthly_sales current\nLEFT JOIN monthly_sales previous\n ON current.product_id = previous.product_id\n AND current.month = previous.month + INTERVAL '1 month';\n```\n\n## Recursive CTEs\n\n```sql\n-- Organizational hierarchy traversal\nWITH RECURSIVE org_hierarchy AS (\n -- Anchor member: top-level managers\n SELECT\n employee_id,\n name,\n manager_id,\n 1 as level,\n ARRAY[employee_id] as path,\n name as hierarchy_path\n FROM employees\n WHERE manager_id IS NULL\n\n UNION ALL\n\n -- Recursive member: employees reporting to current level\n SELECT\n e.employee_id,\n e.name,\n e.manager_id,\n h.level + 1,\n h.path || e.employee_id,\n h.hierarchy_path || ' > ' || e.name\n FROM employees e\n INNER JOIN org_hierarchy h ON e.manager_id = h.employee_id\n WHERE NOT e.employee_id = ANY(h.path) -- Prevent cycles\n)\nSELECT\n employee_id,\n REPEAT(' ', level - 1) || name as indented_name,\n level,\n hierarchy_path\nFROM org_hierarchy\nORDER BY path;\n\n-- Bill of materials (parts explosion)\nWITH RECURSIVE parts_explosion AS (\n SELECT\n part_id,\n component_id,\n quantity,\n 1 as level,\n ARRAY[part_id] as path\n FROM bill_of_materials\n WHERE part_id = 'PRODUCT-123'\n\n UNION ALL\n\n SELECT\n pe.part_id,\n bom.component_id,\n pe.quantity * bom.quantity,\n pe.level + 1,\n pe.path || bom.part_id\n FROM parts_explosion pe\n INNER JOIN bill_of_materials bom ON pe.component_id = bom.part_id\n WHERE NOT bom.part_id = ANY(pe.path)\n)\nSELECT\n component_id,\n SUM(quantity) as total_quantity,\n MAX(level) as max_depth\nFROM parts_explosion\nGROUP BY component_id;\n```\n\n## Advanced JOIN Patterns\n\n```sql\n-- Self-join for finding gaps in sequences\nSELECT\n a.order_id as current_id,\n MIN(b.order_id) as next_id,\n MIN(b.order_id) - a.order_id - 1 as gap_size\nFROM orders a\nLEFT JOIN orders b ON b.order_id > a.order_id\nGROUP BY a.order_id\nHAVING MIN(b.order_id) - a.order_id > 1;\n\n-- LATERAL join for correlated subqueries (PostgreSQL)\nSELECT\n c.customer_id,\n c.name,\n recent.order_date,\n recent.total\nFROM customers c\nCROSS JOIN LATERAL (\n SELECT order_date, total\n FROM orders o\n WHERE o.customer_id = c.customer_id\n ORDER BY order_date DESC\n LIMIT 3\n) recent;\n\n-- Anti-join pattern (records in A not in B)\nSELECT u.user_id, u.email\nFROM users u\nLEFT JOIN orders o ON u.user_id = o.user_id\nWHERE o.order_id IS NULL;\n\n-- Using EXISTS (more efficient than IN for large sets)\nSELECT u.user_id, u.email\nFROM users u\nWHERE NOT EXISTS (\n SELECT 1\n FROM orders o\n WHERE o.user_id = u.user_id\n);\n```\n\n## Subquery Optimization\n\n```sql\n-- Scalar subquery in SELECT (use sparingly - can cause N+1)\nSELECT\n p.product_id,\n p.name,\n (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.product_id) as review_count,\n (SELECT AVG(rating) FROM reviews r WHERE r.product_id = p.product_id) as avg_rating\nFROM products p;\n\n-- Better: Use JOINs with aggregation\nSELECT\n p.product_id,\n p.name,\n COALESCE(r.review_count, 0) as review_count,\n r.avg_rating\nFROM products p\nLEFT JOIN (\n SELECT\n product_id,\n COUNT(*) as review_count,\n AVG(rating) as avg_rating\n FROM reviews\n GROUP BY product_id\n) r ON p.product_id = r.product_id;\n\n-- Correlated subquery for filtering\nSELECT\n order_id,\n customer_id,\n total\nFROM orders o1\nWHERE total > (\n SELECT AVG(total)\n FROM orders o2\n WHERE o2.customer_id = o1.customer_id\n);\n\n-- Better: Use window functions\nSELECT\n order_id,\n customer_id,\n total\nFROM (\n SELECT\n order_id,\n customer_id,\n total,\n AVG(total) OVER (PARTITION BY customer_id) as avg_customer_total\n FROM orders\n) x\nWHERE total > avg_customer_total;\n```\n\n## PIVOT/UNPIVOT Operations\n\n```sql\n-- PostgreSQL CROSSTAB (requires tablefunc extension)\nCREATE EXTENSION IF NOT EXISTS tablefunc;\n\nSELECT * FROM crosstab(\n 'SELECT customer_id, product_category, SUM(amount)\n FROM sales\n GROUP BY customer_id, product_category\n ORDER BY customer_id, product_category',\n 'SELECT DISTINCT product_category FROM sales ORDER BY 1'\n) AS ct(customer_id INT, electronics NUMERIC, clothing NUMERIC, food NUMERIC);\n\n-- Manual PIVOT with CASE\nSELECT\n customer_id,\n SUM(CASE WHEN product_category = 'electronics' THEN amount ELSE 0 END) as electronics,\n SUM(CASE WHEN product_category = 'clothing' THEN amount ELSE 0 END) as clothing,\n SUM(CASE WHEN product_category = 'food' THEN amount ELSE 0 END) as food\nFROM sales\nGROUP BY customer_id;\n\n-- UNPIVOT pattern (row to column)\nSELECT customer_id, 'electronics' as category, electronics as amount\nFROM customer_sales WHERE electronics > 0\nUNION ALL\nSELECT customer_id, 'clothing', clothing\nFROM customer_sales WHERE clothing > 0\nUNION ALL\nSELECT customer_id, 'food', food\nFROM customer_sales WHERE food > 0;\n```\n\n## Set Operations\n\n```sql\n-- UNION for combining distinct results\nSELECT product_id FROM active_products\nUNION\nSELECT product_id FROM featured_products;\n\n-- UNION ALL for better performance (includes duplicates)\nSELECT user_id, 'signup' as event FROM signups WHERE date = CURRENT_DATE\nUNION ALL\nSELECT user_id, 'purchase' as event FROM purchases WHERE date = CURRENT_DATE;\n\n-- INTERSECT for common records\nSELECT email FROM newsletter_subscribers\nINTERSECT\nSELECT email FROM premium_members;\n\n-- EXCEPT for difference (A - B)\nSELECT email FROM all_users\nEXCEPT\nSELECT email FROM unsubscribed_users;\n```\n\n## Performance Tips\n\n1. **CTE Materialization**: PostgreSQL 12+ materializes CTEs by default. Use `WITH cte AS MATERIALIZED` or `NOT MATERIALIZED` to control\n2. **JOIN Order**: Database optimizers handle this, but put smaller tables first in manual optimization\n3. **EXISTS vs IN**: Use EXISTS for correlated checks, IN for small static lists\n4. **Subquery vs JOIN**: Prefer JOINs for readability and optimizer friendliness\n5. **UNION ALL vs UNION**: Use UNION ALL when duplicates are acceptable (no deduplication cost)\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7439,"content_sha256":"19c3b2e803336b5de9edb9fb663d2942f010459bf50f0d2961d5b0c1da6e3a8f"},{"filename":"references/window-functions.md","content":"# Window Functions\n\n## Ranking Functions\n\n```sql\n-- ROW_NUMBER: Sequential numbering within partition\nSELECT\n customer_id,\n order_date,\n total,\n ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as row_num\nFROM orders;\n\n-- Get most recent order per customer\nSELECT *\nFROM (\n SELECT\n customer_id,\n order_id,\n order_date,\n total,\n ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn\n FROM orders\n) ranked\nWHERE rn = 1;\n\n-- RANK: Same values get same rank, gaps in sequence\nSELECT\n student_id,\n score,\n RANK() OVER (ORDER BY score DESC) as rank,\n DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank,\n ROW_NUMBER() OVER (ORDER BY score DESC) as row_num\nFROM exam_results;\n/*\nscore=100: rank=1, dense_rank=1, row_num=1\nscore=100: rank=1, dense_rank=1, row_num=2\nscore=95: rank=3, dense_rank=2, row_num=3\n*/\n\n-- NTILE: Divide into N buckets\nSELECT\n customer_id,\n total_spent,\n NTILE(4) OVER (ORDER BY total_spent DESC) as quartile\nFROM customer_lifetime_value;\n```\n\n## Aggregate Window Functions\n\n```sql\n-- Running totals and cumulative sums\nSELECT\n order_date,\n daily_revenue,\n SUM(daily_revenue) OVER (ORDER BY order_date) as cumulative_revenue,\n AVG(daily_revenue) OVER (\n ORDER BY order_date\n ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n ) as rolling_7day_avg\nFROM daily_sales;\n\n-- Moving average with RANGE\nSELECT\n sale_date,\n amount,\n AVG(amount) OVER (\n ORDER BY sale_date\n RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW\n ) as avg_last_7_days\nFROM sales;\n\n-- Partition-specific aggregates\nSELECT\n product_id,\n sale_date,\n quantity,\n SUM(quantity) OVER (PARTITION BY product_id ORDER BY sale_date) as cumulative_qty,\n AVG(quantity) OVER (PARTITION BY product_id) as avg_qty_for_product,\n quantity::FLOAT / SUM(quantity) OVER (PARTITION BY product_id) as pct_of_total\nFROM product_sales;\n```\n\n## LAG and LEAD Functions\n\n```sql\n-- Compare with previous/next row\nSELECT\n order_date,\n total,\n LAG(total) OVER (ORDER BY order_date) as previous_day_total,\n LEAD(total) OVER (ORDER BY order_date) as next_day_total,\n total - LAG(total) OVER (ORDER BY order_date) as day_over_day_change\nFROM daily_orders;\n\n-- Find gaps in time series\nSELECT\n event_date,\n LAG(event_date) OVER (ORDER BY event_date) as prev_date,\n event_date - LAG(event_date) OVER (ORDER BY event_date) as days_since_last\nFROM events\nWHERE event_date - LAG(event_date) OVER (ORDER BY event_date) > 7;\n\n-- Session analysis with time gaps\nSELECT\n user_id,\n action_time,\n LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time) as prev_action,\n EXTRACT(EPOCH FROM (\n action_time - LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time)\n )) / 60 as minutes_since_last_action,\n CASE\n WHEN EXTRACT(EPOCH FROM (\n action_time - LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time)\n )) / 60 > 30 THEN 1\n ELSE 0\n END as new_session\nFROM user_actions;\n```\n\n## FIRST_VALUE and LAST_VALUE\n\n```sql\n-- Compare each row to first/last in partition\nSELECT\n product_id,\n price_date,\n price,\n FIRST_VALUE(price) OVER (\n PARTITION BY product_id\n ORDER BY price_date\n ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n ) as initial_price,\n LAST_VALUE(price) OVER (\n PARTITION BY product_id\n ORDER BY price_date\n ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n ) as current_price,\n price - FIRST_VALUE(price) OVER (\n PARTITION BY product_id\n ORDER BY price_date\n ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n ) as price_change_from_start\nFROM product_price_history;\n\n-- NTH_VALUE: Get specific positioned value\nSELECT\n sale_date,\n amount,\n NTH_VALUE(amount, 2) OVER (\n ORDER BY sale_date\n ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING\n ) as second_day_amount\nFROM daily_sales;\n```\n\n## Frame Specifications\n\n```sql\n-- ROWS vs RANGE difference\nSELECT\n order_date,\n amount,\n -- ROWS: Physical row offset\n SUM(amount) OVER (\n ORDER BY order_date\n ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING\n ) as sum_5_rows,\n -- RANGE: Logical value range\n SUM(amount) OVER (\n ORDER BY order_date\n RANGE BETWEEN INTERVAL '2 days' PRECEDING AND INTERVAL '2 days' FOLLOWING\n ) as sum_5_day_range\nFROM orders;\n\n-- Common frame patterns\nSELECT\n sale_date,\n revenue,\n -- All preceding rows\n SUM(revenue) OVER (\n ORDER BY sale_date\n ROWS UNBOUNDED PRECEDING\n ) as running_total,\n -- Last 3 rows including current\n AVG(revenue) OVER (\n ORDER BY sale_date\n ROWS BETWEEN 2 PRECEDING AND CURRENT ROW\n ) as ma_3,\n -- Entire partition\n SUM(revenue) OVER (\n PARTITION BY EXTRACT(YEAR FROM sale_date)\n ) as yearly_total,\n -- Centered window\n AVG(revenue) OVER (\n ORDER BY sale_date\n ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING\n ) as centered_ma_7\nFROM sales;\n```\n\n## Advanced Analytics\n\n```sql\n-- Percentile calculations\nSELECT\n employee_id,\n salary,\n PERCENT_RANK() OVER (ORDER BY salary) as pct_rank,\n CUME_DIST() OVER (ORDER BY salary) as cumulative_dist,\n PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () as median_salary,\n PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY salary) OVER () as p90_salary\nFROM employees;\n\n-- Cohort retention analysis\nWITH user_cohorts AS (\n SELECT\n user_id,\n DATE_TRUNC('month', signup_date) as cohort_month,\n DATE_TRUNC('month', activity_date) as activity_month\n FROM user_activity\n),\ncohort_sizes AS (\n SELECT\n cohort_month,\n COUNT(DISTINCT user_id) as cohort_size\n FROM user_cohorts\n GROUP BY cohort_month\n)\nSELECT\n uc.cohort_month,\n uc.activity_month,\n EXTRACT(MONTH FROM AGE(uc.activity_month, uc.cohort_month)) as months_since_signup,\n COUNT(DISTINCT uc.user_id) as active_users,\n cs.cohort_size,\n ROUND(100.0 * COUNT(DISTINCT uc.user_id) / cs.cohort_size, 2) as retention_pct\nFROM user_cohorts uc\nJOIN cohort_sizes cs ON uc.cohort_month = cs.cohort_month\nGROUP BY uc.cohort_month, uc.activity_month, cs.cohort_size\nORDER BY uc.cohort_month, months_since_signup;\n\n-- Time-series gap filling\nSELECT\n date_series.date,\n COALESCE(s.revenue, 0) as revenue,\n AVG(s.revenue) OVER (\n ORDER BY date_series.date\n ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n ) as ma_7day\nFROM generate_series(\n '2024-01-01'::DATE,\n '2024-12-31'::DATE,\n '1 day'::INTERVAL\n) AS date_series(date)\nLEFT JOIN sales s ON date_series.date = s.sale_date;\n```\n\n## Conditional Aggregation with Windows\n\n```sql\n-- Filter within window function\nSELECT\n product_id,\n sale_date,\n quantity,\n SUM(quantity) FILTER (WHERE quantity > 10) OVER (\n PARTITION BY product_id\n ORDER BY sale_date\n ) as cumulative_large_orders,\n COUNT(*) FILTER (WHERE quantity > 100) OVER (\n PARTITION BY product_id\n ) as total_bulk_orders\nFROM sales;\n\n-- Multiple conditions\nSELECT\n customer_id,\n order_date,\n total,\n COUNT(*) FILTER (WHERE total > 1000) OVER (\n PARTITION BY customer_id\n ) as high_value_order_count,\n AVG(total) FILTER (WHERE total \u003c 100) OVER (\n PARTITION BY customer_id\n ) as avg_small_order_value\nFROM orders;\n```\n\n## Performance Considerations\n\n```sql\n-- Avoid multiple window passes - combine into one\n-- Bad: Multiple scans\nSELECT\n product_id,\n (SELECT AVG(price) FROM products) as avg_price,\n (SELECT MAX(price) FROM products) as max_price\nFROM products;\n\n-- Good: Single window pass\nSELECT DISTINCT\n AVG(price) OVER () as avg_price,\n MAX(price) OVER () as max_price\nFROM products;\n\n-- Materialize expensive windows\nCREATE MATERIALIZED VIEW product_rankings AS\nSELECT\n product_id,\n category,\n sales_count,\n RANK() OVER (PARTITION BY category ORDER BY sales_count DESC) as category_rank,\n PERCENT_RANK() OVER (ORDER BY sales_count DESC) as overall_percentile\nFROM product_sales_summary;\n\nCREATE INDEX idx_product_rankings_category ON product_rankings(category, category_rank);\n```\n\n## Common Patterns\n\n1. **Top N per Group**: Use ROW_NUMBER() with WHERE rn \u003c= N\n2. **Running Totals**: SUM() OVER (ORDER BY date)\n3. **Moving Averages**: AVG() with ROWS BETWEEN N PRECEDING\n4. **Session Analysis**: LAG() to detect time gaps\n5. **Deduplication**: ROW_NUMBER() OVER (PARTITION BY key ORDER BY priority) WHERE rn = 1\n6. **Percentiles**: PERCENT_RANK() or PERCENTILE_CONT()\n7. **Year-over-Year**: LAG(value, 12) OVER (ORDER BY month)\n8. **Cohort Analysis**: PARTITION BY cohort_date, aggregate over activity periods\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":8860,"content_sha256":"23f1987aa8223d256502f9ef1504b5aff35999ad6917d85a62c6df4ede7b1769"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"SQL Pro","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Core Workflow","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Schema Analysis","type":"text","marks":[{"type":"strong"}]},{"text":" - Review database structure, indexes, query patterns, performance bottlenecks","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Design","type":"text","marks":[{"type":"strong"}]},{"text":" - Create set-based operations using CTEs, window functions, appropriate joins","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Optimize","type":"text","marks":[{"type":"strong"}]},{"text":" - Analyze execution plans, implement covering indexes, eliminate table scans","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Verify","type":"text","marks":[{"type":"strong"}]},{"text":" - Run ","type":"text"},{"text":"EXPLAIN ANALYZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" and confirm no sequential scans on large tables; if query does not meet sub-100ms target, iterate on index selection or query rewrite before proceeding","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Document","type":"text","marks":[{"type":"strong"}]},{"text":" - Provide query explanations, index rationale, performance metrics","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Reference Guide","type":"text"}]},{"type":"paragraph","content":[{"text":"Load detailed guidance based on context:","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Topic","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Reference","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Load When","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Query Patterns","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"references/query-patterns.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"JOINs, CTEs, subqueries, recursive queries","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Window Functions","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"references/window-functions.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"ROW_NUMBER, RANK, LAG/LEAD, analytics","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Optimization","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"references/optimization.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"EXPLAIN plans, indexes, statistics, tuning","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Database Design","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"references/database-design.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Normalization, keys, constraints, schemas","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Dialect Differences","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"references/dialect-differences.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL vs MySQL vs SQL Server specifics","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick-Reference Examples","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CTE Pattern","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Isolate expensive subquery logic for reuse and readability\nWITH ranked_orders AS (\n SELECT\n customer_id,\n order_id,\n total_amount,\n ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn\n FROM orders\n WHERE status = 'completed' -- filter early, before the join\n)\nSELECT customer_id, order_id, total_amount\nFROM ranked_orders\nWHERE rn = 1; -- latest completed order per customer","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Window Function Pattern","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- Running total and rank within partition — no self-join required\nSELECT\n department_id,\n employee_id,\n salary,\n SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS running_payroll,\n RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank\nFROM employees;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"EXPLAIN ANALYZE Interpretation","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- PostgreSQL: always use ANALYZE to see actual row counts vs. estimates\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT *\nFROM orders o\nJOIN customers c ON c.id = o.customer_id\nWHERE o.created_at > NOW() - INTERVAL '30 days';","type":"text"}]},{"type":"paragraph","content":[{"text":"Key things to check in the output:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Seq Scan on large table","type":"text","marks":[{"type":"strong"}]},{"text":" → add or fix an index","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"actual rows ≫ estimated rows","type":"text","marks":[{"type":"strong"}]},{"text":" → run ","type":"text"},{"text":"ANALYZE \u003ctable>","type":"text","marks":[{"type":"code_inline"}]},{"text":" to refresh statistics","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Buffers: shared hit","type":"text","marks":[{"type":"strong"}]},{"text":" vs ","type":"text"},{"text":"read","type":"text","marks":[{"type":"strong"}]},{"text":" → high ","type":"text"},{"text":"read","type":"text","marks":[{"type":"code_inline"}]},{"text":" count signals missing cache / index","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Before / After Optimization Example","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- BEFORE: correlated subquery, one execution per row (slow)\nSELECT order_id,\n (SELECT SUM(quantity) FROM order_items oi WHERE oi.order_id = o.id) AS item_count\nFROM orders o;\n\n-- AFTER: single aggregation join (fast)\nSELECT o.order_id, COALESCE(agg.item_count, 0) AS item_count\nFROM orders o\nLEFT JOIN (\n SELECT order_id, SUM(quantity) AS item_count\n FROM order_items\n GROUP BY order_id\n) agg ON agg.order_id = o.id;\n\n-- Supporting covering index (includes all columns touched by the query)\nCREATE INDEX idx_order_items_order_qty\n ON order_items (order_id)\n INCLUDE (quantity);","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Constraints","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MUST DO","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Analyze execution plans before recommending optimizations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use set-based operations over row-by-row processing","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Apply filtering early in query execution (before joins where possible)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use EXISTS over COUNT for existence checks","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Handle NULLs explicitly in comparisons and aggregations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create covering indexes for frequent queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Test with production-scale data volumes","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"MUST NOT DO","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use SELECT * in production queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use cursors when set-based operations work","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ignore platform-specific optimizations when targeting a specific dialect","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Implement solutions without considering data volume and cardinality","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Output Templates","type":"text"}]},{"type":"paragraph","content":[{"text":"When implementing SQL solutions, provide:","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Optimized query with inline comments","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Required indexes with rationale","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Execution plan analysis","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Performance metrics (before/after)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Platform-specific notes if applicable","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Maintained by ","type":"text"},{"text":"@jeffallan","type":"text","marks":[{"type":"link","attrs":{"href":"https://github.com/jeffallan","title":null}}]},{"text":", Principal Consultant at ","type":"text"},{"text":"Synergetic Solutions","type":"text","marks":[{"type":"link","attrs":{"href":"https://synergetic.solutions","title":null}}]}]},{"type":"paragraph","content":[{"text":"Documentation","type":"text","marks":[{"type":"link","attrs":{"href":"https://jeffallan.github.io/claude-skills/skills/language/sql-pro/","title":null}}]}]}]},"metadata":{"date":"2026-11-02","name":"sql-pro","author":"@skillopedia","source":{"stars":12181,"repo_name":"claude-skills","origin_url":"https://github.com/Jeffallan/claude-skills/blob/HEAD/skills/sql-pro/SKILL.md","repo_owner":"Jeffallan","body_sha256":"c2916129aa5624e1d0d56dde8722bfe05bcf4a0d49df308ebf1ede5b53676feb","cluster_key":"1dba88307cabdee10a2dd80fd9ee87f49abf52cecb2d780de829fec55b1dbe9d","clean_bundle":{"format":"clean-skill-bundle-v1","source":"Jeffallan/claude-skills/skills/sql-pro/SKILL.md","text_bytes":51610,"source_commit":"078a25cc5bfd8011459be7b91d004e900c1d02d3","skills_sh_rank":3498,"attachment_count":5,"text_attachments":5,"attachment_storage":"skill_attachments","binary_attachments":0},"cluster_size":1,"skill_md_path":"skills/sql-pro/SKILL.md","import_metadata":{"date":"2026-11-02","author":"@skillopedia","version":"v1","category":"data-analytics","category_label":"Data"},"exact_dupes_collapsed_into_this":0},"license":"MIT","version":"v1","category":"data-analytics","metadata":{"role":"specialist","scope":"implementation","author":"https://github.com/Jeffallan","domain":"language","company":"https://synergetic.solutions","version":"1.1.0","triggers":"SQL optimization, query performance, database design, PostgreSQL, MySQL, SQL Server, window functions, CTEs, query tuning, EXPLAIN plan, database indexing","output-format":"code","related-skills":"devops-engineer"},"import_tag":"skills-sh-top20k-2026-11","description":"Optimizes SQL queries, designs database schemas, and troubleshoots performance issues. Use when a user asks why their query is slow, needs help writing complex joins or aggregations, mentions database performance issues, or wants to design or migrate a schema. Invoke for complex queries, window functions, CTEs, indexing strategies, query plan analysis, covering index creation, recursive queries, EXPLAIN/ANALYZE interpretation, before/after query benchmarking, or migrating queries between database dialects (PostgreSQL, MySQL, SQL Server, Oracle)."}},"renderedAt":1793661428589}

SQL Pro Core Workflow 1. Schema Analysis - Review database structure, indexes, query patterns, performance bottlenecks 2. Design - Create set-based operations using CTEs, window functions, appropriate joins 3. Optimize - Analyze execution plans, implement covering indexes, eliminate table scans 4. Verify - Run and confirm no sequential scans on large tables; if query does not meet sub-100ms target, iterate on index selection or query rewrite before proceeding 5. Document - Provide query explanations, index rationale, performance metrics Reference Guide Load detailed guidance based on context:…