SQL & Queries

SQL & Queries — stitch tables back together and do math per group

Mental model: your data is split across tables on purpose (customers in one, their orders in
another, the lines of each order in a third). A query's job is to join those tables back on a
shared key, collapse the joined rows into groups, and run math per group. Read every query as a
pipeline: join -> filter rows (WHERE) -> group -> aggregate -> filter groups (HAVING) -> sort.

The schema (a small normalized set — customers 1-* orders 1-* order_items):

    customers      (id PK, name)
    orders         (id PK, customer_id -> customers.id, total, status)
    order_items    (id PK, order_id -> orders.id, ...)

Seeded on the real run: 3 customers, 4 orders.

1 · The question: "who spent the most?"
    customers and orders live in separate tables, so answering means stitching them on the
    shared key (orders.customer_id -> customers.id) and summing per person.

    SELECT c.name,
           count(o.id)              AS orders,
           coalesce(sum(o.total),0) AS spent
    FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.name
    ORDER BY spent DESC;

    Real result set:
       name | orders | spent
      ------+--------+--------
       Mei  |      1 | 200.00
       Ada  |      2 | 175.50
       Rui  |      1 |  80.00
      (3 rows)

    Mei ranks first at 200.00 from one order, Ada next at 175.50 across two orders, Rui at
    80.00 from one.

2 · JOIN — INNER vs LEFT
    A JOIN combines rows from two tables on a matching key.
      INNER JOIN  keeps only rows that match on BOTH sides.
      LEFT  JOIN  keeps EVERY row from the left table (customers), even when the right side
                  (orders) has nothing — the missing columns come back as NULL.
    This is why a customer with zero orders still appears with a LEFT JOIN. With INNER JOIN
    they would silently vanish, because no order row exists to match.

3 · COALESCE — turn a missing NULL into a clean zero
    For a zero-order customer, sum(o.total) is NULL, not 0. coalesce(sum(o.total),0) returns
    the first non-NULL argument, so the NULL becomes 0.00 in the "spent" column. count(o.id)
    counts only non-NULL order ids, so a zero-order customer correctly shows 0 orders.

4 · GROUP BY — collapse rows into buckets
    GROUP BY c.name puts all of a customer's joined rows into one bucket. Aggregates then run
    once per bucket. Ada has two orders, so her bucket holds two rows that collapse to
    count = 2 and sum = 175.50. Anything in the SELECT that is not aggregated must appear in
    GROUP BY.

5 · Aggregates — count / sum / avg
    count(o.id)   -> how many orders (non-NULL) in the bucket
    sum(o.total)  -> total spend in the bucket
    avg(o.total)  -> average order value in the bucket
    Example (average order value per customer):
    SELECT c.name, round(avg(o.total),2) AS avg_order
    FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.name;

6 · WHERE vs HAVING — the #1 clause-order trap
    WHERE  filters individual rows BEFORE grouping (it cannot see aggregates).
    HAVING filters whole groups AFTER grouping (it filters on the aggregate itself).
    So "only customers who spent over 100" is a HAVING on the summed total, never a WHERE:

    SELECT c.name, coalesce(sum(o.total),0) AS spent
    FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.name
    HAVING coalesce(sum(o.total),0) > 100
    ORDER BY spent DESC;
    -- keeps Mei (200.00) and Ada (175.50); drops Rui (80.00).

7 · ORDER BY — sort the finished result
    ORDER BY runs last, on the assembled result. ORDER BY spent DESC is what put Mei's 200.00
    on top. It sorts the output; it does not change the grouping or the math.

8 · When one pass is not enough — subqueries and window functions
    Subquery: nest a whole query inside another to compare each row against a computed value,
    such as everyone above the average spend:
        SELECT name, spent FROM (
          SELECT c.name, coalesce(sum(o.total),0) AS spent
          FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
          GROUP BY c.name
        ) t
        WHERE spent > (SELECT avg(total) FROM orders);

    Window function: give each row an aggregate over a set of peers WITHOUT collapsing them —
    every order sits beside its customer's running total or rank:
        SELECT o.id, c.name, o.total,
               sum(o.total) OVER (PARTITION BY c.id ORDER BY o.id) AS running_total
        FROM orders o JOIN customers c ON c.id = o.customer_id;

Notes
  - LEFT vs INNER: LEFT JOIN keeps unmatched left rows (zero-order customers stay visible);
    INNER JOIN drops them. Choose LEFT when "none" is a valid, meaningful answer.
  - HAVING filters aggregates: WHERE cannot reference count/sum/avg because it runs before
    grouping. Any condition on an aggregate belongs in HAVING.
  - NULL handling: an aggregate over no rows is NULL, not 0. Wrap it in coalesce(..., 0) so
    "no orders" reads as 0.00 instead of an empty cell, and remember count(col) ignores NULLs.

Verify
  - Re-run the top query and confirm the captured result:
        name = Mei orders = 1 spent = 200.00
        name = Ada orders = 2 spent = 175.50
        name = Rui orders = 1 spent =  80.00
    It should return (3 rows) — one per customer, ordered by spent descending.
  - Row-count check: 3 customers seeded -> 3 grouped rows. The orders column sums to
    1 + 2 + 1 = 4, matching the 4 seeded orders. If a customer is missing, you used INNER JOIN
    instead of LEFT; if "spent" is blank instead of 0.00, you dropped the COALESCE.