spb/ultra-sharp-agent-skills Public

1---2name: writing-sql-queries3description: 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.4---56<!--7Author: Simon-Pierre Boucher8Contact: contact@spboucher.ai9-->1011# Writing SQL Queries1213## When to use / when NOT to use14- **Use for:** authoring or reviewing SQL statements — selects, joins, aggregations, window functions, DML.15- **Do NOT use for:** table/schema design (`designing-database-schemas`), performance tuning (`optimizing-sql-performance`), migration files (`managing-database-migrations`).1617Default dialect: PostgreSQL. Note deviations only when the user names another engine.1819## Core rules20211. **Explicit column lists in production code — never `SELECT *`.**22   - ✅ `SELECT id, email, created_at FROM users;`23   - ❌ `SELECT * FROM users;` (breaks on schema change, over-fetches)24   `SELECT *` is fine for interactive exploration only.25262. **Explicit `JOIN … ON`, never comma joins.**27   - ✅ `FROM orders o JOIN users u ON u.id = o.user_id`28   - ❌ `FROM orders o, users u WHERE u.id = o.user_id`29303. **CTEs over nested subqueries once there is more than one level.**31   - ✅ `WITH recent AS (SELECT …) SELECT … FROM recent JOIN …`32   - ❌ `SELECT … FROM (SELECT … FROM (SELECT …) a) b`33344. **Window functions for ranking and running totals — not self-joins.**35   - ✅ `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)`36   - ❌ correlated subquery counting rows "before this one"37385. **NULL is not a value.**39   - ✅ `WHERE deleted_at IS NULL` / `WHERE id NOT IN (SELECT … WHERE x IS NOT NULL)` or use `NOT EXISTS`40   - ❌ `WHERE deleted_at = NULL` (always false) · ❌ `NOT IN` against a set containing NULL (returns no rows)41   Default to `NOT EXISTS` over `NOT IN` for subqueries.42436. **Every non-aggregated selected column appears in `GROUP BY`.** Aggregate everything else explicitly; filter groups with `HAVING`, rows with `WHERE`.44457. **Parameterized queries ALWAYS — never string interpolation.**46   - ✅ `cur.execute("SELECT id FROM users WHERE email = %s", (email,))`47   - ❌ `f"SELECT id FROM users WHERE email = '{email}'"` (SQL injection)48498. **Format for review:** keywords UPPERCASE, one clause per line, short meaningful aliases (`users u`, not `users a`).5051## Workflow52531. Restate what the query must return (columns, grain, filters) in one line.542. Write the query following the rules above.553. 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.564. If it fails or returns the wrong grain, fix and re-run before delivering.5758## Edge cases & failure modes59- **Unknown schema** → inspect first (`\d table` / `information_schema.columns`); never guess column names.60- **Dialect mismatch** (e.g. `LIMIT` vs `TOP`, `||` vs `CONCAT`) → confirm engine, adjust per notes in references.61- **Division** → guard with `NULLIF(denominator, 0)`.62- **Timezones** → compare `timestamptz` in UTC; never compare naive and aware timestamps.6364## References65Copy-paste patterns and dialect notes: see [references/patterns.md](references/patterns.md).66