Data SQL Skill

Version: 1.0 Stack: SQL (PostgreSQL, Redshift, Athena, Spark SQL)

SQL is the only language where a working query can be 1,000x slower than an equivalent working query. A missing index turns a 50ms lookup into a 50-second full table scan. SELECT * breaks downstream code when someone adds a column. Correlated subqueries silently execute N+1 patterns that disappear in development and cripple production. The query planner doesn't warn you — it just does what you asked.

SQL written with index awareness, explicit columns, and CTEs is both faster to execute and easier to debug. Clarity and performance reinforce each other.

Scope and Boundaries

This skill covers:

• Query optimization patterns

• Schema design and normalization

• Data modeling (star schema, slowly changing dimensions)

• CTEs and query organization

• Index strategy

• Window functions

Defers to other skills:

• security : SQL injection prevention, parameterized queries

• data-pipelines : When to use SQL vs. application code

Use this skill when: Writing SQL queries or designing schemas.

Core Principles

• CTEs for Readability — Break complex queries into named steps.

• Filter Early — Push WHERE clauses as close to source as possible.

• Explicit Columns — Never SELECT * in production code.

• Index-Aware Queries — Know what's indexed, write queries that use them.

• Idempotent Operations — INSERT/UPDATE should be safe to retry.

Patterns

CTE Organization

-- Good - logical steps, easy to debug WITH active_users AS ( SELECT user_id, email FROM users WHERE status = 'active' ), recent_orders AS ( SELECT user_id, COUNT(*) as order_count FROM orders WHERE created_at > CURRENT_DATE - INTERVAL '30 days' GROUP BY user_id ) SELECT u.email, COALESCE(o.order_count, 0) as recent_orders FROM active_users u LEFT JOIN recent_orders o USING (user_id);

Upsert Pattern (PostgreSQL)

INSERT INTO inventory (sku, quantity, updated_at) VALUES ('ABC123', 100, NOW()) ON CONFLICT (sku) DO UPDATE SET quantity = EXCLUDED.quantity, updated_at = EXCLUDED.updated_at;

Window Functions

-- Running total and row number SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) as running_total, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rank FROM daily_sales;

Anti-Patterns

Anti-Pattern Problem Fix

SELECT *

Breaks on schema changes, wastes bandwidth List explicit columns

Correlated subqueries N+1 query behavior Use JOINs or window functions

OR in JOIN conditions Prevents index use Restructure or use UNION

Functions on indexed columns WHERE YEAR(date) = 2024

Use range: date >= '2024-01-01'

Missing GROUP BY columns Undefined behavior Include all non-aggregated columns

Checklist

• No SELECT * in production queries

• CTEs used for complex queries

• WHERE clauses filter early

• JOINs prefer indexed columns

• Upserts are idempotent

• Large queries tested with EXPLAIN

References

• references/query-optimization.md — Index usage, join optimization, performance

• references/window-functions.md — Window function patterns and examples

Assets

• assets/query-patterns.md — Common SQL patterns and templates