SQL / PostgreSQL cheat sheet

-- 1. Parenthesize AND/OR precedence; half-open timestamp window.
SELECT id, amount, currency FROM transactions
WHERE status IN ('FAILED', 'REJECTED') AND amount >= 100
  AND created_at >= TIMESTAMPTZ '2026-10-01 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2026-10-02 00:00:00+00'
ORDER BY created_at, id LIMIT 50;

-- 2. NULL comparisons require IS NULL; COALESCE sets a reporting default.
SELECT id, COALESCE(email, '(missing)') AS contact,
  CASE WHEN email IS NULL THEN 'MISSING' ELSE 'PRESENT' END AS quality
FROM users WHERE email IS NULL OR email ILIKE '%@example.test';

-- 3. BETWEEN is inclusive on both ends; use intentionally for numeric bounds.
SELECT id FROM transactions WHERE amount BETWEEN 10 AND 20
  AND NOT (status = 'FAILED' OR status = 'REJECTED');

-- 4. Stable keyset pagination; next cursor is last (created_at,id).
SELECT id, created_at FROM transactions
WHERE (created_at, id) > (TIMESTAMPTZ '2026-10-01 12:00:00+00', 'tx-100')
ORDER BY created_at, id LIMIT 100;

-- 5. Aggregate per currency: COUNT(*) counts rows, COUNT(amount) excludes nulls.
SELECT currency, COUNT(*) AS rows, COUNT(amount) AS amounts,
       SUM(amount) AS total, AVG(amount) AS mean, MIN(amount), MAX(amount)
FROM transactions GROUP BY currency HAVING COUNT(*) > 1;

-- 6. Duplicate business value; decide normalization rules before grouping.
SELECT email, COUNT(*) FROM users
WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1;

-- 7. DISTINCT removes repeated result tuples, not an explanation for duplication.
SELECT DISTINCT user_id, status FROM transactions ORDER BY user_id, status;

-- 8. Missing source IDs in target: count equality alone cannot prove completeness.
SELECT s.id FROM staged_transactions s
WHERE NOT EXISTS (SELECT 1 FROM transactions t WHERE t.id = s.id);

-- 9. Mismatched totals with pre-aggregation avoids join fanout.
WITH source AS (
  SELECT currency, SUM(amount) total FROM staged_transactions GROUP BY currency
), target AS (
  SELECT currency, SUM(amount) total FROM transactions GROUP BY currency
)
SELECT COALESCE(s.currency, t.currency) AS currency, s.total AS expected, t.total AS actual
FROM source s FULL JOIN target t USING (currency)
WHERE s.total IS DISTINCT FROM t.total;

-- 10. Orphan transaction (if integrity constraints permit historical bad rows).
SELECT t.id FROM transactions t LEFT JOIN users u ON u.id = t.user_id
WHERE u.id IS NULL;

-- 11. Users with successful transactions, no duplicate user rows.
SELECT u.id FROM users u WHERE EXISTS (
  SELECT 1 FROM transactions t WHERE t.user_id = u.id AND t.status = 'COMPLETED'
);

-- 12. Missing event projection after a controlled watermark/lag window.
SELECT e.id, e.correlation_id FROM events e
LEFT JOIN projections p ON p.event_id = e.id
WHERE p.event_id IS NULL AND e.created_at < CURRENT_TIMESTAMP - INTERVAL '2 minutes';

-- 13. Explicit INNER/RIGHT examples; each event may multiply user rows.
SELECT u.id, e.id AS event_id FROM users u INNER JOIN events e ON e.user_id = u.id;
SELECT u.id, e.id AS event_id FROM users u RIGHT JOIN events e ON e.user_id = u.id;

-- 14. Latest event per user; deterministic tie-breaker.
WITH ranked AS (
  SELECT e.*, ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY created_at DESC, id DESC
  ) AS rn FROM events e
)
SELECT user_id, id, payload FROM ranked WHERE rn = 1;

-- 15. RANK ties share rank and leave gaps; LAG compares preceding row.
SELECT id, user_id, amount,
  RANK() OVER (PARTITION BY currency ORDER BY amount DESC) AS amount_rank,
  LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at, id) AS previous_amount
FROM transactions;

-- 16. JSONB: -> returns JSON, ->> returns text, @> tests containment.
SELECT id, payload->'identity' AS identity,
       payload->>'status' AS status
FROM events WHERE payload @> '{"status":"VERIFIED"}'::jsonb;

-- 17. UTC representation: timestamptz stores an instant, display depends on session TZ.
SELECT id, created_at AT TIME ZONE 'UTC' AS created_utc
FROM events ORDER BY created_at DESC LIMIT 20;

-- 18. Safe multi-query reconciliation snapshot; rollback ends read-only investigation.
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT COUNT(*) FROM staged_transactions;
SELECT COUNT(*) FROM transactions;
ROLLBACK;

-- 19. EXPLAIN estimates; EXPLAIN ANALYZE actually executes the statement.
EXPLAIN SELECT id FROM transactions WHERE user_id = 'qa-user' ORDER BY created_at DESC;

// 20. Scoped node-postgres example; optional `pg` dependency, injected client.
// client.query supports positional parameters; never concatenate untrusted IDs.
const result = await client.query('SELECT id, status FROM users WHERE id = $1', [userId]);