Chapter 3 · 9 min

Joining pre-aggregated results

Many interview questions combine a per-entity metric with descriptive columns: customer email plus order count, product name plus units sold, country plus revenue. The shape of the answer is a join between a grouped subquery and the table that holds the description.

WITH customer_orders AS (
  SELECT customer_id, count(*) AS paid_orders
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT c.customer_id, c.email, co.paid_orders
FROM customers c
JOIN customer_orders co ON co.customer_id = c.customer_id
WHERE co.paid_orders > 3
ORDER BY co.paid_orders DESC;

The grouping happens first, so the metric is computed on the right grain. The outer query adds columns without changing that grain. If the join key is unique on the aggregated side, the result keeps one row per customer; if it is not, the metric fans out and the number is wrong.

Keep the filter at the right grain

The WHERE co.paid_orders > 3 predicate applies to the aggregated result, not to individual orders. Putting the same threshold on orders before grouping would remove orders instead of customers. This is the HAVING lesson applied to a joined subquery: conditions on aggregates belong after aggregation, wherever that aggregation is written.

Left join for zeroes

If the report must include customers with no orders, use a left join from the descriptive table to the aggregate and coalesce the missing metric:

SELECT c.customer_id, c.email, COALESCE(co.paid_orders, 0) AS paid_orders
FROM customers c
LEFT JOIN customer_orders co ON co.customer_id = c.customer_id;

An inner join here returns only customers who have orders. The choice between inner and left is the same choice as in the outer-join lesson, applied one level up: does the question require every entity, or only the ones with data?

Aggregate both sides only when the grain matches

Joining two pre-aggregated results is safe when their group keys describe the same entity. Joining a per-customer aggregate to a per-order aggregate multiplies rows again, because one customer has many orders. Match the grain before matching the keys:

WITH customer_totals AS (
  SELECT o.customer_id, sum(oi.quantity * oi.unit_price) AS lifetime_value
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status IN ('paid', 'shipped')
  GROUP BY o.customer_id
)
SELECT c.email, customer_totals.lifetime_value
FROM customers c
JOIN customer_totals ON customer_totals.customer_id = c.customer_id
ORDER BY customer_totals.lifetime_value DESC;

Note the aggregate is computed on the many side and grouped back to the customer grain before the join. That ordering is what keeps the number honest.

What to say in an interview

Describe the grain of every relation before joining them: one row per customer, one row per order, one row per line item. Then state how the join changes that grain. An answer that names the grain out loud is much easier to trust than one that only shows SQL, because the SQL can be correct and still answer a different question.