spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# Patterns — Writing SQL Queries78## Contents9- CTE pipelines10- Window functions (ranking, running totals, deduplication)11- NULL-safe predicates12- Upserts13- Aggregation patterns14- Parameterized queries by language15- Dialect deviations (MySQL, SQLite)16- Gotchas1718## CTE pipelines1920```sql21WITH recent_orders AS (22    SELECT user_id, total_cents, created_at23    FROM orders24    WHERE created_at >= now() - INTERVAL '30 days'25),26user_totals AS (27    SELECT user_id, SUM(total_cents) AS spend_cents28    FROM recent_orders29    GROUP BY user_id30)31SELECT u.id, u.email, t.spend_cents32FROM users u33JOIN user_totals t ON t.user_id = u.id34ORDER BY t.spend_cents DESC;35```3637## Window functions3839Latest row per group (deduplication):4041```sql42SELECT id, user_id, status43FROM (44    SELECT o.*,45           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn46    FROM orders o47) ranked48WHERE rn = 1;49```5051Running total and rank:5253```sql54SELECT created_at::date AS day,55       SUM(total_cents) AS day_total,56       SUM(SUM(total_cents)) OVER (ORDER BY created_at::date) AS running_total,57       RANK() OVER (ORDER BY SUM(total_cents) DESC) AS day_rank58FROM orders59GROUP BY created_at::date;60```6162`ROW_NUMBER` = no ties · `RANK` = ties skip numbers · `DENSE_RANK` = ties don't skip.6364## NULL-safe predicates6566```sql67-- Anti-join: rows in users with no orders. Prefer NOT EXISTS.68SELECT u.id69FROM users u70WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);7172-- NULL-safe equality (PostgreSQL):73WHERE a IS NOT DISTINCT FROM b7475-- Safe division:76SELECT paid_cents::numeric / NULLIF(total_cents, 0) AS paid_ratio;77```7879## Upserts8081```sql82INSERT INTO settings (user_id, key, value)83VALUES ($1, $2, $3)84ON CONFLICT (user_id, key)85DO UPDATE SET value = EXCLUDED.value, updated_at = now();86```8788## Aggregation patterns8990Conditional aggregation (pivot-lite):9192```sql93SELECT user_id,94       COUNT(*) FILTER (WHERE status = 'paid')    AS paid_count,95       COUNT(*) FILTER (WHERE status = 'refunded') AS refunded_count96FROM orders97GROUP BY user_id;98```99100`HAVING` filters groups, `WHERE` filters rows — apply `WHERE` first for less work:101102```sql103SELECT user_id, COUNT(*) AS n104FROM orders105WHERE created_at >= '2026-01-01'106GROUP BY user_id107HAVING COUNT(*) >= 5;108```109110## Parameterized queries by language111112```python113# psycopg (PostgreSQL)114cur.execute("SELECT id FROM users WHERE email = %s", (email,))115# sqlite3116cur.execute("SELECT id FROM users WHERE email = ?", (email,))117```118119```javascript120// node-postgres121await pool.query('SELECT id FROM users WHERE email = $1', [email]);122```123124Identifiers (table/column names) cannot be parameters — validate them against an allowlist if dynamic.125126## Dialect deviations (MySQL, SQLite)127128| PostgreSQL | MySQL | SQLite |129|---|---|---|130| `ON CONFLICT … DO UPDATE` | `ON DUPLICATE KEY UPDATE` | `ON CONFLICT … DO UPDATE` (3.24+) |131| `COUNT(*) FILTER (WHERE …)` | `SUM(CASE WHEN … THEN 1 ELSE 0 END)` | same as MySQL |132| `now() - INTERVAL '30 days'` | `NOW() - INTERVAL 30 DAY` | `datetime('now', '-30 days')` |133| `::type` cast | `CAST(x AS type)` | `CAST(x AS type)` |134| `string \|\| string` | `CONCAT(a, b)` (`\|\|` needs PIPES_AS_CONCAT) | `\|\|` |135136## Gotchas137138- `NOT IN (subquery)` returns zero rows if the subquery yields any NULL — use `NOT EXISTS`.139- `COUNT(col)` skips NULLs; `COUNT(*)` counts rows. Different numbers on nullable columns.140- `UNION` deduplicates (and sorts on many engines); `UNION ALL` is what you usually want.141- Integer division truncates in PostgreSQL: `1/2 = 0`. Cast one side to `numeric`.142- `ORDER BY` in a subquery/CTE is not guaranteed to survive to the outer query — order at the outermost level.143- `BETWEEN a AND b` is inclusive on both ends; for timestamp ranges use `>= a AND < b_next` to avoid double-counting boundaries.144- Window functions cannot appear in `WHERE`/`HAVING` — wrap in a subquery (see deduplication pattern).145