<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# Patterns — Writing SQL Queries

## Contents
- CTE pipelines
- Window functions (ranking, running totals, deduplication)
- NULL-safe predicates
- Upserts
- Aggregation patterns
- Parameterized queries by language
- Dialect deviations (MySQL, SQLite)
- Gotchas

## CTE pipelines

```sql
WITH recent_orders AS (
    SELECT user_id, total_cents, created_at
    FROM orders
    WHERE created_at >= now() - INTERVAL '30 days'
),
user_totals AS (
    SELECT user_id, SUM(total_cents) AS spend_cents
    FROM recent_orders
    GROUP BY user_id
)
SELECT u.id, u.email, t.spend_cents
FROM users u
JOIN user_totals t ON t.user_id = u.id
ORDER BY t.spend_cents DESC;
```

## Window functions

Latest row per group (deduplication):

```sql
SELECT id, user_id, status
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;
```

Running total and rank:

```sql
SELECT created_at::date AS day,
       SUM(total_cents) AS day_total,
       SUM(SUM(total_cents)) OVER (ORDER BY created_at::date) AS running_total,
       RANK() OVER (ORDER BY SUM(total_cents) DESC) AS day_rank
FROM orders
GROUP BY created_at::date;
```

`ROW_NUMBER` = no ties · `RANK` = ties skip numbers · `DENSE_RANK` = ties don't skip.

## NULL-safe predicates

```sql
-- Anti-join: rows in users with no orders. Prefer NOT EXISTS.
SELECT u.id
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- NULL-safe equality (PostgreSQL):
WHERE a IS NOT DISTINCT FROM b

-- Safe division:
SELECT paid_cents::numeric / NULLIF(total_cents, 0) AS paid_ratio;
```

## Upserts

```sql
INSERT INTO settings (user_id, key, value)
VALUES ($1, $2, $3)
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = now();
```

## Aggregation patterns

Conditional aggregation (pivot-lite):

```sql
SELECT user_id,
       COUNT(*) FILTER (WHERE status = 'paid')    AS paid_count,
       COUNT(*) FILTER (WHERE status = 'refunded') AS refunded_count
FROM orders
GROUP BY user_id;
```

`HAVING` filters groups, `WHERE` filters rows — apply `WHERE` first for less work:

```sql
SELECT user_id, COUNT(*) AS n
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY user_id
HAVING COUNT(*) >= 5;
```

## Parameterized queries by language

```python
# psycopg (PostgreSQL)
cur.execute("SELECT id FROM users WHERE email = %s", (email,))
# sqlite3
cur.execute("SELECT id FROM users WHERE email = ?", (email,))
```

```javascript
// node-postgres
await pool.query('SELECT id FROM users WHERE email = $1', [email]);
```

Identifiers (table/column names) cannot be parameters — validate them against an allowlist if dynamic.

## Dialect deviations (MySQL, SQLite)

| PostgreSQL | MySQL | SQLite |
|---|---|---|
| `ON CONFLICT … DO UPDATE` | `ON DUPLICATE KEY UPDATE` | `ON CONFLICT … DO UPDATE` (3.24+) |
| `COUNT(*) FILTER (WHERE …)` | `SUM(CASE WHEN … THEN 1 ELSE 0 END)` | same as MySQL |
| `now() - INTERVAL '30 days'` | `NOW() - INTERVAL 30 DAY` | `datetime('now', '-30 days')` |
| `::type` cast | `CAST(x AS type)` | `CAST(x AS type)` |
| `string \|\| string` | `CONCAT(a, b)` (`\|\|` needs PIPES_AS_CONCAT) | `\|\|` |

## Gotchas

- `NOT IN (subquery)` returns zero rows if the subquery yields any NULL — use `NOT EXISTS`.
- `COUNT(col)` skips NULLs; `COUNT(*)` counts rows. Different numbers on nullable columns.
- `UNION` deduplicates (and sorts on many engines); `UNION ALL` is what you usually want.
- Integer division truncates in PostgreSQL: `1/2 = 0`. Cast one side to `numeric`.
- `ORDER BY` in a subquery/CTE is not guaranteed to survive to the outer query — order at the outermost level.
- `BETWEEN a AND b` is inclusive on both ends; for timestamp ranges use `>= a AND < b_next` to avoid double-counting boundaries.
- Window functions cannot appear in `WHERE`/`HAVING` — wrap in a subquery (see deduplication pattern).