# name: writing-sql-queries description: Writes correct, readable, injection-safe SQL — explicit columns and joins, CTEs, window functions, NULL-safe predicates, parameterized queries. Use when the user asks to write, fix, or review a SQL query, SELECT/INSERT/UPDATE/DELETE statement, join, aggregation, ranking, or running total, or asks "query the database for X". Do not use for designing tables or schemas, tuning slow queries or indexes, or writing schema migrations — separate skills cover those.

# Writing SQL Queries

# When to use / when NOT to use

• Use for: authoring or reviewing SQL statements — selects, joins, aggregations, window functions, DML.
• Do NOT use for: table/schema design (designing-database-schemas), performance tuning (optimizing-sql-performance), migration files (managing-database-migrations).

Default dialect: PostgreSQL. Note deviations only when the user names another engine.

# Core rules

1. Explicit column lists in production code — never SELECT *.

   • ✅ SELECT id, email, created_at FROM users;
   • ❌ SELECT * FROM users; (breaks on schema change, over-fetches) SELECT * is fine for interactive exploration only.

2. Explicit JOIN … ON, never comma joins.

   • ✅ FROM orders o JOIN users u ON u.id = o.user_id
   • ❌ FROM orders o, users u WHERE u.id = o.user_id

3. CTEs over nested subqueries once there is more than one level.

   • ✅ WITH recent AS (SELECT …) SELECT … FROM recent JOIN …
   • ❌ SELECT … FROM (SELECT … FROM (SELECT …) a) b

4. Window functions for ranking and running totals — not self-joins.

   • ✅ ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)
   • ❌ correlated subquery counting rows "before this one"

5. NULL is not a value.

   • ✅ WHERE deleted_at IS NULL / WHERE id NOT IN (SELECT … WHERE x IS NOT NULL) or use NOT EXISTS
   • ❌ WHERE deleted_at = NULL (always false) · ❌ NOT IN against a set containing NULL (returns no rows) Default to NOT EXISTS over NOT IN for subqueries.

6. Every non-aggregated selected column appears in GROUP BY. Aggregate everything else explicitly; filter groups with HAVING, rows with WHERE.

7. Parameterized queries ALWAYS — never string interpolation.

   • ✅ cur.execute("SELECT id FROM users WHERE email = %s", (email,))
   • ❌ f"SELECT id FROM users WHERE email = '{email}'" (SQL injection)

8. Format for review: keywords UPPERCASE, one clause per line, short meaningful aliases (users u, not users a).

# Workflow

1. Restate what the query must return (columns, grain, filters) in one line.
2. Write the query following the rules above.
3. Validate: run it (or EXPLAIN it if data is unavailable) against the target engine; check the row grain with a LIMIT 10 sample and, for aggregates, a known-total sanity check.
4. If it fails or returns the wrong grain, fix and re-run before delivering.

# Edge cases & failure modes

• Unknown schema → inspect first (\d table / information_schema.columns); never guess column names.
• Dialect mismatch (e.g. LIMIT vs TOP, || vs CONCAT) → confirm engine, adjust per notes in references.
• Division → guard with NULLIF(denominator, 0).
• Timezones → compare timestamptz in UTC; never compare naive and aware timestamps.

# References

Copy-paste patterns and dialect notes: see references/patterns.md.