SQL

SQL#

Most examples target PostgreSQL; differences noted where MySQL or SQLite diverge.

Top N per Group#

-- Latest order per customer
SELECT *
FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM   orders o
) x
WHERE rn = 1;

Running Total#

SELECT id,
       amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         AS running_total
FROM orders;

Lead / Lag#

SELECT id,
       amount,
       LAG(amount)  OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount,
       LEAD(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS next_amount,
       amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS delta
FROM orders;

Percentiles#

SELECT percentile_cont(0.50) WITHIN GROUP (ORDER BY duration_ms) AS p50,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY duration_ms) AS p95,
       percentile_cont(0.99) WITHIN GROUP (ORDER BY duration_ms) AS p99
FROM   requests
WHERE  created_at >= now() - interval '1 hour';

Upsert#

PostgreSQL:

INSERT INTO users (id, email, name, last_seen)
VALUES (1, 'operator@example.com', 'Ada', now())
ON CONFLICT (id) DO UPDATE
  SET email     = EXCLUDED.email,
      name      = EXCLUDED.name,
      last_seen = EXCLUDED.last_seen;

MySQL:

INSERT INTO users (id, email, name, last_seen)
VALUES (1, 'operator@example.com', 'Ada', NOW())
ON DUPLICATE KEY UPDATE
  email     = VALUES(email),
  name      = VALUES(name),
  last_seen = VALUES(last_seen);

SQLite:

INSERT INTO users (id, email, name, last_seen)
VALUES (1, 'operator@example.com', 'Ada', strftime('%s','now'))
ON CONFLICT(id) DO UPDATE SET
  email     = excluded.email,
  name      = excluded.name,
  last_seen = excluded.last_seen;

RETURNING (Postgres)#

INSERT INTO users (email) VALUES ('operator@example.com')
RETURNING id, created_at;

UPDATE users SET last_seen = now() WHERE id = 1
RETURNING last_seen;

DELETE FROM sessions WHERE expires_at < now()
RETURNING id;

JSON Operations#

PostgreSQL JSON / JSONB operators, one example per operator.

Top-level field access with ->>.

SELECT data->>'name' AS name FROM events;

Nested-path access with #>> and a path array.

SELECT data#>>'{user,email}' FROM events;

Containment filter with @>.

SELECT * FROM events WHERE data @> '{"type": "click"}';

Aggregate rows into a JSON array.

SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name))
FROM users;

Spread a JSON array into rows with jsonb_array_elements.

SELECT id, x->>'name' AS name, (x->>'age')::int AS age
FROM events, LATERAL jsonb_array_elements(data->'users') x;

Date Bucketing#

Bucket rows into one row per day. Days with no rows are absent from the result.

SELECT date_trunc('day', created_at) AS day,
       COUNT(*) AS orders,
       SUM(total) AS revenue
FROM   orders
WHERE  created_at >= now() - interval '30 days'
GROUP  BY day
ORDER  BY day;

Use generate_series and a LEFT JOIN when the result must include zero-rows for empty days.

SELECT g.day,
       COALESCE(o.orders, 0) AS orders
FROM   generate_series(date_trunc('day', now() - interval '29 days'),
                       date_trunc('day', now()),
                       interval '1 day') AS g(day)
LEFT JOIN (
  SELECT date_trunc('day', created_at) AS day, COUNT(*) AS orders
  FROM   orders GROUP BY 1
) o ON o.day = g.day
ORDER BY g.day;

Pagination#

Offset-based (simple, slow at high offsets):

SELECT * FROM posts
ORDER BY id
LIMIT 20 OFFSET 100;

Keyset (recommended):

SELECT * FROM posts
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

Recursive CTE#

WITH RECURSIVE org AS (
  SELECT id, manager_id, 1 AS depth
  FROM   employees
  WHERE  manager_id IS NULL

  UNION ALL

  SELECT e.id, e.manager_id, o.depth + 1
  FROM   employees e
  JOIN   org o ON e.manager_id = o.id
)
SELECT * FROM org;

Lateral Join#

-- Most recent 3 orders per customer
SELECT c.id, o.*
FROM   customers c
LEFT JOIN LATERAL (
  SELECT * FROM orders
  WHERE  customer_id = c.id
  ORDER  BY created_at DESC
  LIMIT  3
) o ON true;

EXPLAIN#

Plain EXPLAIN ANALYZE runs the query and reports the actual times.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 1 ORDER BY created_at DESC LIMIT 10;

Add BUFFERS to surface cache hits and VERBOSE for column detail.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ...;

Index Recipes#

Compound index over the columns of a common query.

CREATE INDEX idx_orders_customer_recent
  ON orders (customer_id, created_at DESC);

Partial index over an active subset of rows.

CREATE INDEX idx_active_users ON users (created_at)
WHERE active;

Functional index over the result of an expression.

CREATE UNIQUE INDEX idx_users_email_lower
  ON users (lower(email));

Concurrent build avoids taking a table lock during the build (Postgres).

CREATE INDEX CONCURRENTLY idx_x ON t (col);

Common Constraints#

A non-negative CHECK constraint.

ALTER TABLE orders
  ADD CONSTRAINT orders_total_nonneg CHECK (total >= 0);

An enumerated-status CHECK constraint.

ALTER TABLE orders
  ADD CONSTRAINT orders_status_valid
  CHECK (status IN ('pending','paid','shipped','cancelled'));

A functional UNIQUE constraint.

ALTER TABLE orders
  ADD CONSTRAINT orders_email_unique UNIQUE (lower(email));

Transactions#

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- ROLLBACK on error

-- Savepoints
BEGIN;
  INSERT INTO ...;
  SAVEPOINT s1;
  UPDATE ...;          -- if this fails:
  ROLLBACK TO s1;      -- ... we keep the INSERT
COMMIT;

SQL

Contents