SQL & data access

What the question asks                        What you reach for
"for each X, how many / total"                GROUP BY X with COUNT or SUM
"only the X with more than N"                 HAVING, not WHERE
"bring in the customer's name"                INNER JOIN
"including the ones with none"                LEFT JOIN
"rank within each X", "the latest per X"      window function, PARTITION BY
"running total", "compared to last month"     window function, SUM OVER / LAG
"the ones that have no matching row"          LEFT JOIN ... WHERE right IS NULL,
                                              or NOT EXISTS
"why is this slow"                            EXPLAIN ANALYZE, then an index

customers                        orders
id  name        country          id   customer_id  amount  status    created_at
1   Ada         UK               101  1            120.00  paid      2026-01-04
2   Grace       US               102  1             40.00  paid      2026-02-11
3   Alan        UK               103  2            300.00  paid      2026-01-19
4   Katherine   US               104  2             90.00  refunded  2026-03-02
                                 105  3             15.00  paid      2026-03-08
                                 106  9             55.00  paid      2026-03-09

Katherine has no orders. Order 106 has no customer.

SELECT   customer_id, COUNT(*) AS orders, SUM(amount) AS revenue
FROM     orders
WHERE    status = 'paid'
GROUP BY customer_id
HAVING   SUM(amount) > 100
ORDER BY revenue DESC
LIMIT    20;

-- customer_id | orders | revenue
-- 2           | 1      | 300.00
-- 1           | 2      | 160.00

written       runs        what exists at that point
FROM     1.   FROM        the raw rows, joined
WHERE    2.   WHERE       individual rows, no groups yet, no aggregates
GROUP BY 3.   GROUP BY    rows collapsed into one row per group
HAVING   4.   HAVING      groups, so aggregates are now available
SELECT   5.   SELECT      the output columns and their aliases are created here
ORDER BY 6.   ORDER BY    aliases from SELECT are usable
LIMIT    7.   LIMIT       the first N of whatever ORDER BY produced

SELECT COUNT(*)                  AS rows_,        -- 6: every row
       COUNT(amount)             AS with_amount,  -- rows where amount IS NOT NULL
       COUNT(DISTINCT customer_id) AS customers,  -- 4: distinct values
       SUM(amount)               AS total,        -- 620.00
       AVG(amount)               AS mean,         -- ignores NULLs in the divisor too
       COUNT(*) FILTER (WHERE status = 'refunded') AS refunds   -- 1
FROM orders;

-- INNER JOIN: only rows that matched on both sides.
SELECT c.name, o.id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

-- name   | id  | amount
-- Ada    | 101 | 120.00
-- Ada    | 102 |  40.00
-- Grace  | 103 | 300.00
-- Grace  | 104 |  90.00
-- Alan   | 105 |  15.00
--
-- 5 rows. Katherine is gone (no orders) and order 106 is gone (no customer).

-- LEFT JOIN: every row from the left, nulls where the right had no match.
SELECT c.name, o.id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- name       | id   | amount
-- Ada        | 101  | 120.00
-- Ada        | 102  |  40.00
-- Grace      | 103  | 300.00
-- Grace      | 104  |  90.00
-- Alan       | 105  |  15.00
-- Katherine  | NULL | NULL     <- kept, padded with nulls
--
-- 6 rows. Order 106 is still gone: it is on the right.

-- RIGHT JOIN: the mirror image. Every order, nulls where no customer matched.
SELECT c.name, o.id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

-- name   | id  | amount
-- Ada    | 101 | 120.00
-- ...
-- NULL   | 106 |  55.00   <- the orphan order, kept

-- FULL OUTER JOIN: both sides kept, nulls on whichever side is missing.
-- 7 rows: the 5 matched, plus Katherine, plus order 106.

-- CROSS JOIN: every combination, no condition. 4 customers x 6 orders = 24 rows.
-- Rarely wanted deliberately. Very often produced by accident.

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;          -- only the rows the join could not fill

-- id | name
-- 4  | Katherine

SELECT c.id, c.name,
       COUNT(*)      AS wrong,
       COUNT(o.id)   AS right_
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- id | name      | wrong | right_
-- 1  | Ada       | 2     | 2
-- 2  | Grace     | 2     | 2
-- 3  | Alan      | 1     | 1
-- 4  | Katherine | 1     | 0     <- COUNT(*) counted the padded row

SELECT id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id)                        AS customer_total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC)  AS rn,
       ROUND(amount / SUM(amount) OVER (), 3)                             AS share_of_all
FROM orders;

-- id  | customer_id | amount | customer_total | rn | share_of_all
-- 101 | 1           | 120.00 | 160.00         | 1  | 0.194
-- 102 | 1           |  40.00 | 160.00         | 2  | 0.065
-- 103 | 2           | 300.00 | 390.00         | 1  | 0.484
-- 104 | 2           |  90.00 | 390.00         | 2  | 0.145
-- 105 | 3           |  15.00 |  15.00         | 1  | 0.024
-- 106 | 9           |  55.00 |  55.00         | 1  | 0.089

-- Most recent order per customer: rank, then keep the first of each.
WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM orders o
)
SELECT id, customer_id, amount, created_at
FROM ranked
WHERE rn = 1;

-- id  | customer_id | amount | created_at
-- 102 | 1           |  40.00 | 2026-02-11
-- 104 | 2           |  90.00 | 2026-03-02
-- 105 | 3           |  15.00 | 2026-03-08
-- 106 | 9           |  55.00 | 2026-03-09

-- Running total and change against the previous row.
SELECT created_at, amount,
       SUM(amount) OVER (ORDER BY created_at)             AS running_total,
       amount - LAG(amount) OVER (ORDER BY created_at)    AS change_vs_prev
FROM orders;

-- created_at | amount | running_total | change_vs_prev
-- 2026-01-04 | 120.00 | 120.00        | NULL      <- no previous row
-- 2026-01-19 | 300.00 | 420.00        |  180.00
-- 2026-02-11 |  40.00 | 460.00        | -260.00
-- 2026-03-02 |  90.00 | 550.00        |   50.00
-- 2026-03-08 |  15.00 | 565.00        |  -75.00
-- 2026-03-09 |  55.00 | 620.00        |   40.00

values:      10  10   9   8
ROW_NUMBER    1   2   3   4     ties broken arbitrarily, never repeats
RANK          1   1   3   4     ties share, then skip
DENSE_RANK    1   1   2   3     ties share, no gap

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

-- Before the index:
-- Seq Scan on orders  (cost=0.00..18334.00 rows=6 width=64)
--                     (actual time=0.412..84.203 rows=6 loops=1)
--   Filter: (customer_id = 42)
--   Rows Removed by Filter: 999994
-- Planning Time: 0.094 ms
-- Execution Time: 84.256 ms

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

-- After:
-- Index Scan using orders_customer_id_idx on orders
--                     (cost=0.42..25.10 rows=6 width=64)
--                     (actual time=0.028..0.041 rows=6 loops=1)
--   Index Cond: (customer_id = 42)
-- Execution Time: 0.068 ms

What you see                         What it means
Seq Scan, large table                 no usable index, or the planner chose
                                      not to use one because most rows match
Index Scan                            what you wanted
Bitmap Heap Scan                       index found many rows; collecting them first
Rows Removed by Filter: 999994        99.99% of the work was thrown away
rows=6 ... actual rows=800000         the estimate is wrong; run ANALYZE
Nested Loop, actual loops=50000       a join running per-row; usually the slow part
Sort ... external merge Disk: 84MB    it ran out of work_mem and sorted on disk

-- These cannot use a plain index on email:
WHERE lower(email) = 'ada@example.com'      -- fix: CREATE INDEX ... ON t (lower(email))
WHERE email LIKE '%example.com'             -- leading wildcard
WHERE user_id::text = '42'                  -- cast on the column, not the value

-- A composite index, and what it covers:
CREATE INDEX ON llm_calls (user_id, created_at);
WHERE user_id = 42                            -- used
WHERE user_id = 42 AND created_at > '...'     -- used, both columns
WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20   -- used, and no sort needed
WHERE created_at > '...'                      -- not used: wrong leading column

Asked for                                    Pattern
"the three largest per customer"             ROW_NUMBER in a CTE, filter rn <= 3
"the latest row per user"                    the same, with ORDER BY updated_at DESC
"cost per day, cumulative"                   SUM(x) OVER (ORDER BY day)
"this month against last"                    LAG over an ordered window
"errors and calls per model, one row each"   COUNT(*) FILTER, or SUM(CASE WHEN)
"a column per category"                      conditional aggregation
"users with no events"                       NOT EXISTS
"days with no data should still appear"      generate the dates, LEFT JOIN to them
"deduplicate this table"                     rank per key, keep rn = 1

WITH paid AS (
  SELECT customer_id, amount, created_at
  FROM orders
  WHERE status = 'paid'
),
per_customer AS (
  SELECT customer_id, SUM(amount) AS revenue, COUNT(*) AS orders
  FROM paid
  GROUP BY customer_id
)
SELECT c.name, p.revenue, p.orders
FROM per_customer p
JOIN customers c ON c.id = p.customer_id
ORDER BY p.revenue DESC;

WITH ranked AS (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS rn
  FROM orders
)
SELECT customer_id, id, amount
FROM ranked
WHERE rn <= 3;

SELECT DISTINCT ON (customer_id) customer_id, id, amount, created_at
FROM orders
ORDER BY customer_id, created_at DESC, id DESC;   -- latest order per customer

SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day)                         AS running_total,
       revenue - LAG(revenue) OVER (ORDER BY day)               AS change_vs_prev_day,
       AVG(revenue) OVER (ORDER BY day
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS avg_7d
FROM daily_revenue;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW    running total (the default shape)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW           trailing 7 rows
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING           centred, 7 rows
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   the whole partition

WITH latest AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC, id DESC) AS rn
  FROM profile_versions
)
SELECT * FROM latest WHERE rn = 1;

-- profile_versions          result
-- user  updated_at   val    user  updated_at   val
-- 1     2026-01-01   a      1     2026-02-01   b
-- 1     2026-02-01   b      2     2026-01-05   d
-- 2     2026-01-05   c
-- 2     2026-01-05   d      <- same timestamp; id DESC decides

SELECT model,
       COUNT(*)                                   AS calls,
       COUNT(*) FILTER (WHERE status = 'error')   AS errors,
       COUNT(*) FILTER (WHERE status = 'error')::float / COUNT(*)  AS error_rate,
       COUNT(DISTINCT user_id)                    AS users,
       AVG(latency_ms)                            AS avg_latency,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_latency
FROM llm_calls
GROUP BY model;

SELECT date_trunc('day', created_at) AS day,
       SUM(CASE WHEN model = 'haiku'  THEN cost_usd ELSE 0 END) AS haiku,
       SUM(CASE WHEN model = 'sonnet' THEN cost_usd ELSE 0 END) AS sonnet,
       SUM(CASE WHEN model = 'opus'   THEN cost_usd ELSE 0 END) AS opus
FROM llm_calls
GROUP BY 1
ORDER BY 1;

-- tier: 'pro', NULL, 'free', 'pro'      amount: 10, 20, NULL, 5

SELECT COUNT(*) FROM t WHERE tier = NULL;              -- 0, never any rows
SELECT COUNT(*) FROM t WHERE tier IS NULL;             -- 1
SELECT COUNT(*) FROM t WHERE tier <> 'pro';            -- 1, not 2
SELECT COUNT(*) FROM t WHERE tier IS DISTINCT FROM 'pro';   -- 2, the NULL included

SELECT SUM(amount), AVG(amount), COUNT(amount), COUNT(*) FROM t;
-- 35.0 | 11.67 | 3 | 4      AVG divided by 3, not 4

SELECT 'tier: ' || tier FROM t;    -- NULL for the row where tier is NULL,
                                   -- not the string 'tier: '

-- Looks like a LEFT JOIN. Behaves like an INNER JOIN.
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';          -- Katherine's NULL status fails this, so she is dropped

-- What was meant:
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

EXPLAIN ANALYZE
SELECT * FROM llm_calls WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

-- Limit  (actual time=612.8..612.8 rows=20 loops=1)
--   ->  Sort  (actual time=612.8..612.8 rows=20 loops=1)
--         Sort Key: created_at DESC
--         Sort Method: top-N heapsort  Memory: 41kB
--         ->  Seq Scan on llm_calls  (actual time=0.3..598.1 rows=8140 loops=1)
--               Filter: (user_id = 42)
--               Rows Removed by Filter: 4991860
-- Execution Time: 613.2 ms

CREATE INDEX llm_calls_user_created_idx ON llm_calls (user_id, created_at DESC);

-- Limit  (actual time=0.04..0.07 rows=20 loops=1)
--   ->  Index Scan using llm_calls_user_created_idx on llm_calls
--         Index Cond: (user_id = 42)
-- Execution Time: 0.09 ms

BEGIN;
UPDATE accounts SET credits = credits - 10 WHERE id = 7;
INSERT INTO usage (account_id, credits) VALUES (7, 10);
COMMIT;   -- both happen, or on error neither does

-- ROLLBACK undoes everything since BEGIN. Most client libraries do it for you
-- when the block raises:
-- with conn.transaction():
--     ...            # an exception here rolls back and re-raises

CREATE TABLE usage (
  id          bigserial PRIMARY KEY,
  account_id  bigint NOT NULL REFERENCES accounts(id),
  credits     int    NOT NULL CHECK (credits > 0),
  request_id  text   NOT NULL UNIQUE,     -- one row per request, however many retries
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Retry is harmless: the second attempt inserts nothing and raises nothing.
INSERT INTO usage (account_id, credits, request_id)
VALUES (7, 10, 'req_8f2c')
ON CONFLICT (request_id) DO NOTHING;

-- Upsert: insert, or update what is already there. The excluded pseudo-table
-- holds the row you tried to insert.
INSERT INTO daily_cost (day, model, cost_usd)
VALUES ('2026-03-11', 'sonnet', 12.40)
ON CONFLICT (day, model) DO UPDATE
SET cost_usd = daily_cost.cost_usd + excluded.cost_usd;

-- Return what happened, so the caller knows without a second query.
INSERT INTO usage (account_id, credits, request_id)
VALUES (7, 10, 'req_8f2c')
ON CONFLICT (request_id) DO NOTHING
RETURNING id;    -- no row returned means it was already there

time   request A                     request B
 1     SELECT credits -> 10
 2                                   SELECT credits -> 10
 3     UPDATE SET credits = 0
 4                                   UPDATE SET credits = 0
       one of the two charges has vanished, and nothing raised

-- 1. Do the arithmetic in the database. One statement, no gap.
UPDATE accounts SET credits = credits - 10
WHERE id = 7 AND credits >= 10
RETURNING credits;      -- no row returned means there were not enough credits

-- 2. Lock the row first, if you must read, decide, then write.
BEGIN;
SELECT credits FROM accounts WHERE id = 7 FOR UPDATE;  -- B blocks here until A commits
-- ... decide ...
UPDATE accounts SET credits = credits - 10 WHERE id = 7;
COMMIT;

-- 3. Optimistic: carry a version and fail if it moved.
UPDATE accounts SET credits = 0, version = version + 1
WHERE id = 7 AND version = 4;     -- 0 rows updated means someone else got there first