The One-Screen SQL Cheatsheet: CRUD, DDL, Window Functions, Dialect Traps

Not a tutorial — a tab you keep open. Assumes you know what a join is. Primary dialect is Postgres 16; MySQL divergences are flagged where they'll actually burn you. When you're pasting something ugly to check it against this page, run it through the formatter first so the structure matches what's below.

The order you write vs the order that runs

You write SELECT ... FROM ... WHERE .... The engine runs, roughly:

FROM / JOIN      -- assemble the row source
WHERE            -- filter raw rows (no aggregates yet)
GROUP BY         -- collapse to groups
HAVING           -- filter groups (aggregates allowed here)
SELECT           -- expressions, aliases computed NOW
DISTINCT         -- dedupe after SELECT
UNION            -- set ops
ORDER BY         -- can see SELECT aliases
LIMIT / OFFSET   -- last
WINDOW functions -- evaluated at SELECT stage, after aggregates, before DISTINCT

Three bugs this explains for free: WHERE COUNT(*) > 5 fails (aggregates postdate WHERE — that's what HAVING is for); a WHERE on a LEFT JOIN's null-side column deletes the rows the join just preserved; and window functions see filtered-but-not-yet-limited rows, so WHERE rn = 1 needs the rank computed in a CTE or subquery.

CRUD skeletons

-- C: multi-row insert, take back what you inserted (Postgres; MySQL 8 has no RETURNING at all — see the hacks page)
INSERT INTO users (email, status) VALUES
  ('[email protected]', 'active'),
  ('[email protected]', 'pending')
RETURNING id, created_at;

-- R: the canonical read
SELECT u.email, COUNT(o.id) AS orders
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.email
HAVING COUNT(o.id) > 0
ORDER BY orders DESC, u.email   -- tiebreaker makes pagination stable
LIMIT 20 OFFSET 40;

-- U: join-update (PG; MySQL uses multi-table UPDATE syntax)
UPDATE orders AS o
SET status = 'refunded'
FROM refunds AS r
WHERE r.order_id = o.id AND o.status = 'paid';

-- D: delete with a returning audit trail
DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING id;

DDL essentials

CREATE TABLE IF NOT EXISTS events (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind        TEXT NOT NULL,
  payload     JSONB NOT NULL DEFAULT '{}',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX CONCURRENTLY idx_events_kind_time
  ON events (kind, created_at DESC);   -- CONCURRENTLY: PG only, no table lock
CREATE UNIQUE INDEX CONCURRENTLY uq_events_dedupe
  ON events (kind, payload->>'uuid');  -- expression index for idempotency

Two rules: always TIMESTAMPTZ, never TIMESTAMP (see patterns and gotchas); and CREATE INDEX CONCURRENTLY in production — plain CREATE INDEX takes a SHARE lock that blocks writes for the whole build.

Window functions: 8 skeletons

-- 1. Dedupe: latest row per group
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
-- then: WHERE rn = 1  (in an outer query/CTE — you can't filter a window in its own WHERE)

-- 2. Rank with ties handled honestly
RANK()       OVER w   AS r   -- 1,1,3  (skips)
DENSE_RANK() OVER w   AS dr  -- 1,1,2  (no skips)
-- leaderboard: RANK so #2 means "exactly one person strictly ahead"

-- 3. Change since previous row
LAG(revenue, 1, 0) OVER (PARTITION BY acct ORDER BY month) AS prev
-- revenue - prev, the 3rd arg is the default for the first row

-- 4. First/last in partition
FIRST_VALUE(city) OVER (PARTITION BY acct ORDER BY ts)
-- cheaper idiom for "last": reverse the order and take LAG, or use (ARRAY_AGG(city ORDER BY ts DESC))[1]

-- 5. Conditional aggregate without GROUP BY (PG)
COUNT(*) FILTER (WHERE status = 'failed') OVER (PARTITION BY job_id)
-- MySQL: COUNT(CASE WHEN ... THEN 1 END) — no FILTER

-- 6. Bucket into quartiles
NTILE(4) OVER (ORDER BY score DESC) AS quartile
-- 7. Running total / moving average
SUM(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- 8. Frame it explicitly when ORDER BY has ties
SUM(x) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW)
-- default frame is RANGE UNBOUNDED PRECEDING — ties all share one total; use ROWS if that surprises you

CTEs, minimal forms

WITH recent AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT user_id, COUNT(*) FROM recent GROUP BY user_id;

-- Recursive: traverse a tree. UNION ALL mandatory; depth guard mandatory in prod.
WITH RECURSIVE subtree AS (
  SELECT id, parent_id, 1 AS depth FROM nodes WHERE id = 42
  UNION ALL
  SELECT n.id, n.parent_id, s.depth + 1
  FROM nodes n JOIN subtree s ON n.parent_id = s.id
  WHERE s.depth < 20
)
SELECT * FROM subtree;

Postgres 12+ inlines most CTEs; add MATERIALIZED when you want the old fence-post behavior, NOT MATERIALIZED to force inlining. MySQL 8 supports recursive CTEs but no MATERIALIZED keyword — see the Nov calendar entry for the porting traps.

Postgres vs MySQL flag sheet

FAQ

Is WHERE or HAVING faster? Not a speed question — a stage question. WHERE runs before aggregation so it reduces work; HAVING runs after. If your filter doesn't reference an aggregate, it belongs in WHERE and the planner will thank you.

Why does my recursive CTE hang? Cycles. A→B→A loops forever because UNION ALL doesn't dedupe. Add a depth cap, or track the path: WHERE NOT (n.id = ANY(path)) alongside ARRAY[n.id] || path.

Does formatting change any of this? No, which is why it's worth doing — see best practices. The window frame, the missing tiebreaker, and the poisoned NOT IN all become visible once the query has structure to inspect.