# 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).