Getting the data out

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