Practice: Layered Query Problems

Decompose it by grain. Every layer of a query operates at a row granularity, and the most common cause of a wrong answer is a mismatch between the grain of one layer and the next. Start by writing down, in one sentence, what one row of the final output represents — "one row per customer per status" — and then work backwards from there.

The standard layers are: filter rows, join to add columns, aggregate to a grain, filter the aggregates, and finally reshape or rank. Each layer changes the number of rows, so the order is not arbitrary. Filters that can apply to raw rows should be applied before the aggregation, because they change what the aggregates mean. Filters on aggregates have to come after. Joins that multiply rows must come before any aggregate you do not want inflated, or after it if you aggregate in a derived table first.

Three habits make this concrete. First, build and check one layer at a time — run the query without the final ORDER BY and look at the row count at each stage, because a row count that surprises you is the earliest signal that a join fanned out. Second, name every intermediate result, whether with a CTE or a derived table, because an unnamed nested subquery is where grain mistakes hide. Third, keep the raw and the derived columns in the output while you are debugging, so you can see which value came from where.

And when a query is genuinely hard, decompose it into two queries. Compute the aggregate in one, then filter or join it in another. The two-step version is not a compromise — it is usually the answer, because it lets you check the intermediate numbers against a source of truth before you trust the final output.

The three layers are a join, a GROUP BY, and a HAVING. The order is forced: you cannot aggregate before joining, and you cannot filter on an aggregate before aggregating it.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  country VARCHAR(60) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name, country) VALUES
  (1, 'Ada Lovelace', 'UK'),
  (2, 'Grace Hopper', 'US'),
  (3, 'Alan Turing',  'UK'),
  (4, 'Joan Clarke',  'US');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2, 940.00),
  (4, 2,  75.25),
  (5, 3,  60.00),
  (6, 3, 210.00),
  (7, 3, 180.00);

SELECT c.name,
       c.country,
       COUNT(o.id) AS order_count,
       ROUND(SUM(o.amount), 2) AS total_spend
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.country
HAVING SUM(o.amount) > 400
ORDER BY total_spend DESC;

Three rows come back, ordered by spend: Grace Hopper with 1015.25 from two orders, Ada Lovelace with 460.50 from two orders, and Alan Turing with 450.00 from three orders. All three clear the 400 threshold.

Joan Clarke is the one who drops out: she has no orders at all, so the inner join removes her before the grouping happens. If the report should include her with a count of zero, the join has to be a LEFT JOIN and the aggregate has to be protected — COUNT(o.id) gives 0 for her, but SUM(o.amount) gives NULL, and a NULL total does not satisfy the HAVING clause, so she would still be filtered out.

Two details worth pointing out in review. The GROUP BY lists c.id along with the two selected columns, which is the safe form: it guarantees one group per customer even if two customers shared a name. And the filter is written on SUM(amount) rather than on the total_spend alias — both work in SQLite, but repeating the aggregate is portable to every engine, and the alias version fails when the expression includes a rounding function that has to be applied after the filter rather than before it.