SQL

CREATE TABLE customers (id integer PRIMARY KEY, name text NOT NULL, region text);
CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  status text NOT NULL,                       -- 'pending', 'paid', 'cancelled'
  amount numeric(12,2) NOT NULL,
  created_at timestamp NOT NULL
);
CREATE INDEX orders_customer_created ON orders (customer_id, created_at DESC);

-- customers with their orders; customers without orders are absent
SELECT c.name, o.id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

-- every customer, order columns NULL where none exist
SELECT c.name, o.id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- customers with no paid order: the condition on the outer table goes in ON, the NULL test in WHERE
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
WHERE o.id IS NULL;

-- same thing, usually the better plan and immune to duplicate rows
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid');

-- self join: pairs of orders from the same customer on the same day, each pair once
SELECT a.id, b.id
FROM orders a
JOIN orders b ON b.customer_id = a.customer_id
             AND date(b.created_at) = date(a.created_at)
             AND b.id > a.id;

-- cross join: every combination, here to build a calendar grid
SELECT r.region, s.status FROM (SELECT DISTINCT region FROM customers) r CROSS JOIN (VALUES ('pending'), ('paid'), ('cancelled')) AS s(status);

SELECT c.region,
       count(*)                                   AS orders,
       count(DISTINCT o.customer_id)              AS customers,
       sum(o.amount)                              AS revenue,
       avg(o.amount)                              AS avg_order,
       sum(o.amount) FILTER (WHERE o.status = 'paid') AS paid_revenue,   -- PostgreSQL and SQLite 3.30+
       sum(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END) AS paid_revenue_portable
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= date '2026-01-01'             -- row filter: cuts work before grouping
GROUP BY c.region
HAVING count(*) >= 10                                 -- group filter
ORDER BY revenue DESC;

SELECT id, customer_id, created_at, amount,
       row_number() OVER w                                  AS n,             -- 1,2,3 with no ties
       rank()       OVER w                                  AS rnk,           -- 1,1,3 on ties
       dense_rank() OVER w                                  AS drnk,          -- 1,1,2 on ties
       lag(amount)  OVER w                                  AS prev_amount,   -- NULL for the first row
       lead(created_at) OVER w - created_at                 AS gap_to_next,   -- interval in PostgreSQL
       sum(amount)  OVER (PARTITION BY customer_id ORDER BY created_at
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       avg(amount)  OVER (PARTITION BY customer_id ORDER BY created_at
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS moving_avg_7,
       sum(amount)  OVER (PARTITION BY customer_id)         AS customer_total,   -- whole partition
       amount / sum(amount) OVER (PARTITION BY customer_id) AS share,
       first_value(amount) OVER w                           AS first_amount,
       ntile(4) OVER (ORDER BY amount)                      AS quartile
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at);   -- named window; inline the definition on MySQL < 8.0 or omit

WITH paid AS (
  SELECT customer_id, sum(amount) AS total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
), ranked AS (
  SELECT c.region, c.name, p.total,
         rank() OVER (PARTITION BY c.region ORDER BY p.total DESC) AS r
  FROM paid p JOIN customers c ON c.id = p.customer_id
)
SELECT region, name, total FROM ranked WHERE r <= 3 ORDER BY region, r;

CREATE TABLE employees (id integer PRIMARY KEY, name text, manager_id integer REFERENCES employees(id));

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth, name AS path          -- anchor: the root
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, chain.depth + 1, chain.path || ' > ' || e.name   -- CONCAT() on MySQL
  FROM employees e
  JOIN chain ON e.manager_id = chain.id
  WHERE chain.depth < 20                                           -- guard against a cycle
)
SELECT * FROM chain ORDER BY path;

-- generate a series without a helper function (SQLite, MySQL); PostgreSQL has generate_series()
WITH RECURSIVE days(d) AS (
  SELECT date '2026-01-01'
  UNION ALL
  SELECT d + 1 FROM days WHERE d < date '2026-01-31'                -- date_add(d, INTERVAL 1 DAY) on MySQL, date(d, '+1 day') on SQLite
)
SELECT d FROM days;

-- semi-join: each customer once, however many paid orders they have
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid');

-- the join version needs DISTINCT to undo the multiplication
SELECT DISTINCT c.name FROM customers c JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

-- scalar subquery per row: fine for small outer sets
SELECT c.name, (SELECT max(created_at) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;

-- LATERAL (PostgreSQL 9.3+, MySQL 8.0.14+) runs a subquery per outer row and can return several columns or rows
SELECT c.name, o.id, o.amount
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, amount FROM orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 3
) o;

-- PostgreSQL 9.5+ and SQLite 3.24+
INSERT INTO customers (id, name, region)
VALUES (42, 'Acme', 'VIC')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,                                   -- EXCLUDED is the row that failed to insert
    region = coalesce(EXCLUDED.region, customers.region)
WHERE customers.name IS DISTINCT FROM EXCLUDED.name;          -- skip no-op updates (IS NOT on SQLite)

INSERT INTO customers (id, name) VALUES (42, 'Acme') ON CONFLICT DO NOTHING;   -- insert-if-absent
INSERT INTO customers (id, name) VALUES (42, 'Acme') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name RETURNING id, (xmax = 0) AS inserted;  -- PostgreSQL: was it an insert

-- MySQL 8.0.19+ (row alias); MariaDB and older MySQL use VALUES(name)
INSERT INTO customers (id, name, region)
VALUES (42, 'Acme', 'VIC') AS new
ON DUPLICATE KEY UPDATE name = new.name, region = new.region;

INSERT INTO customers (id, name, region) VALUES (42, 'Acme', 'VIC')
ON DUPLICATE KEY UPDATE name = VALUES(name), region = VALUES(region);   -- MariaDB 11.x; deprecated in MySQL 8.0.20+

INSERT IGNORE INTO customers (id, name) VALUES (42, 'Acme');   -- insert-if-absent, but also silences other errors: prefer ON DUPLICATE KEY UPDATE id = id

CREATE INDEX orders_customer_created ON orders (customer_id, created_at DESC);   -- serves WHERE customer_id = ? ORDER BY created_at DESC
CREATE INDEX orders_paid_created ON orders (created_at) WHERE status = 'paid';   -- partial index: PostgreSQL, SQLite
CREATE UNIQUE INDEX customers_lower_name ON customers (lower(name));            -- expression index: PostgreSQL, SQLite; MySQL 8.0.13+ with ((lower(name)))
CREATE INDEX orders_customer_incl ON orders (customer_id) INCLUDE (amount);     -- covering columns not in the key: PostgreSQL 11+

EXPLAIN (ANALYZE, BUFFERS)                             -- PostgreSQL: run it and report real rows, times and I/O
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

EXPLAIN ANALYZE                                        -- MySQL 8.0.18+: tree format with actual rows and loops
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

EXPLAIN QUERY PLAN                                     -- SQLite: SCAN vs SEARCH ... USING INDEX
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;          -- blocks other FOR UPDATE / UPDATE on this row until COMMIT
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

BEGIN ISOLATION LEVEL SERIALIZABLE;                            -- PostgreSQL; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE on MySQL before BEGIN
-- ... on ERROR 40001 (PostgreSQL) or 1213/1205 (MySQL), retry the whole transaction
COMMIT;

SELECT * FROM jobs WHERE state = 'queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;   -- queue pattern: PostgreSQL 9.5+, MySQL 8
SAVEPOINT before_risky; ... ROLLBACK TO SAVEPOINT before_risky;                        -- partial rollback inside a transaction

SELECT NULL = NULL;                      -- NULL, not true
SELECT * FROM customers WHERE region <> 'VIC';       -- excludes rows where region IS NULL
SELECT * FROM customers WHERE region <> 'VIC' OR region IS NULL;
SELECT * FROM customers WHERE region IS DISTINCT FROM 'VIC';    -- PostgreSQL; SQLite: region IS NOT 'VIC'; MySQL: NOT (region <=> 'VIC')
SELECT coalesce(region, 'unknown'), nullif(region, '') FROM customers;    -- nullif turns '' into NULL
SELECT count(*), count(region), sum(amount) FROM ...;                      -- aggregates skip NULL; sum of nothing is NULL
SELECT ... ORDER BY region NULLS LAST;   -- PostgreSQL, SQLite 3.30+; MySQL sorts NULL first ascending, use ORDER BY region IS NULL, region
SELECT 1 FROM t WHERE x NOT IN (SELECT y FROM u);    -- empty result if any y is NULL

-- PostgreSQL
SELECT now(), current_date, now() AT TIME ZONE 'Australia/Melbourne';
SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1;
SELECT created_at + interval '30 days', age(now(), created_at), extract(epoch FROM created_at);
SELECT * FROM orders WHERE created_at >= date '2026-09-01' AND created_at < date '2026-10-01';   -- half-open range uses the index

-- MySQL / MariaDB
SELECT NOW(), CURDATE(), CONVERT_TZ(created_at, 'UTC', 'Australia/Melbourne');
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month, SUM(amount) FROM orders GROUP BY month;
SELECT DATE_ADD(created_at, INTERVAL 30 DAY), TIMESTAMPDIFF(DAY, created_at, NOW()), UNIX_TIMESTAMP(created_at);

-- SQLite
SELECT datetime('now'), date('now', 'localtime'), strftime('%Y-%m-01', created_at) AS month;
SELECT datetime(created_at, '+30 days'), julianday('now') - julianday(created_at) AS days_ago, unixepoch(created_at);

-- first page
SELECT id, created_at, amount FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- next page: pass the last row's (created_at, id); row-value comparison works in PostgreSQL, MySQL 8, SQLite
SELECT id, created_at, amount FROM orders
WHERE customer_id = 42
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- portable expansion of the row comparison
WHERE customer_id = 42 AND (created_at < :last_created_at OR (created_at = :last_created_at AND id < :last_id))

-- duplicates by a key, with the count
SELECT name, region, count(*) FROM customers GROUP BY name, region HAVING count(*) > 1;

-- delete duplicates, keep the lowest id (PostgreSQL, SQLite; on MySQL use a self join or a temp table because a subquery cannot target the same table)
DELETE FROM customers
WHERE id IN (
  SELECT id FROM (
    SELECT id, row_number() OVER (PARTITION BY name, region ORDER BY id) AS n FROM customers
  ) d WHERE n > 1
);

-- gaps in a sequence of ids
SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (SELECT id, lead(id) OVER (ORDER BY id) AS next_id FROM orders) s
WHERE next_id > id + 1;

-- islands: consecutive days with at least one order, grouped into runs
WITH days AS (SELECT DISTINCT date(created_at) AS d FROM orders),
     grp  AS (SELECT d, d - (row_number() OVER (ORDER BY d))::int AS g FROM days)   -- MySQL: DATE_SUB(d, INTERVAL ROW_NUMBER() OVER (...) DAY)
SELECT min(d) AS run_start, max(d) AS run_end, count(*) AS days FROM grp GROUP BY g ORDER BY 1;

-- top 3 orders per customer
SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS n FROM orders o
) t WHERE n <= 3;

-- latest row per group (PostgreSQL shortcut)
SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, created_at DESC;

-- latest row per group, portable, using an anti-join
SELECT o.* FROM orders o
LEFT JOIN orders newer ON newer.customer_id = o.customer_id AND (newer.created_at, newer.id) > (o.created_at, o.id)
WHERE newer.id IS NULL;

-- pivot: one row per region, one column per status
SELECT c.region,
       sum(CASE WHEN o.status = 'pending'   THEN o.amount ELSE 0 END) AS pending,
       sum(CASE WHEN o.status = 'paid'      THEN o.amount ELSE 0 END) AS paid,
       sum(CASE WHEN o.status = 'cancelled' THEN o.amount ELSE 0 END) AS cancelled
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY c.region;

-- unpivot: columns back into rows (PostgreSQL; MySQL and SQLite use UNION ALL of one SELECT per column)
SELECT region, v.status, v.amount
FROM region_totals r
CROSS JOIN LATERAL (VALUES ('pending', r.pending), ('paid', r.paid), ('cancelled', r.cancelled)) AS v(status, amount);

-- month-over-month change
WITH m AS (SELECT date_trunc('month', created_at) AS month, sum(amount) AS revenue FROM orders GROUP BY 1)
SELECT month, revenue, revenue - lag(revenue) OVER (ORDER BY month) AS delta,
       round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month)) / lag(revenue) OVER (ORDER BY month), 1) AS pct
FROM m ORDER BY month;

-- rows in one table missing from another (both directions)
SELECT id FROM staging EXCEPT SELECT id FROM orders;          -- MySQL 8.0.31+ and MariaDB support EXCEPT; older MySQL: NOT EXISTS
SELECT id FROM orders  EXCEPT SELECT id FROM staging;

-- percentile
SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY amount) FROM orders;      -- PostgreSQL; MySQL: window PERCENT_RANK or ntile approximation

-- histogram in buckets of 100
SELECT (amount / 100)::int * 100 AS bucket, count(*) FROM orders GROUP BY 1 ORDER BY 1;   -- FLOOR(amount / 100) * 100 on MySQL and SQLite

-- cumulative distinct users by day
SELECT d, count(*) OVER (ORDER BY d) AS cumulative_customers
FROM (SELECT customer_id, min(date(created_at)) AS d FROM orders GROUP BY customer_id) f;

-- random sample of about 1% (PostgreSQL); ORDER BY RAND() LIMIT n on MySQL and SQLite is a full sort
SELECT * FROM orders TABLESAMPLE SYSTEM (1);

-- lock a batch for processing, others skip it
UPDATE jobs SET state = 'running', worker = :worker
WHERE id IN (SELECT id FROM jobs WHERE state = 'queued' ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED)
RETURNING id;                                                                  -- MySQL 8: no RETURNING; select the ids first inside the transaction

-- rows updated in the last hour and who changed them, for a table with audit columns
SELECT id, updated_by, updated_at FROM orders WHERE updated_at >= now() - interval '1 hour' ORDER BY updated_at DESC;