Join Drills from Real Interviews

The inflation comes from the join fanning out before the count happens. One order with three line items becomes three rows after the join, and counting rows in that result counts the same order three times. The order is not duplicated in the database — your query duplicated it.

The fix is to count a column that belongs to the many side, and to group by a key from the one side:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  placed_on TEXT NOT NULL
);

CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product TEXT NOT NULL,
  quantity INTEGER NOT NULL
);

INSERT INTO orders (id, customer, placed_on) VALUES
  (1, 'Ada', '2024-01-05'),
  (2, 'Grace', '2024-01-06');

INSERT INTO order_items (id, order_id, product, quantity) VALUES
  (1, 1, 'keyboard', 1),
  (2, 1, 'mouse', 2),
  (3, 1, 'monitor', 1),
  (4, 2, 'laptop', 1);

SELECT o.id, o.customer, COUNT(i.id) AS item_count
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.customer;

Order 1 comes back with a count of 3, order 2 with 1. Note the join is a LEFT JOIN, so an order with no items still appears — with a count of 0 rather than disappearing from the report. If you use an inner join instead, order-less orders vanish silently, which is a different bug with the same symptom of missing rows.

Two details worth raising in an interview. First, count i.id, not COUNT(*): COUNT(*) counts rows and would return 1 for an order with no items, because the LEFT JOIN still produces one row. COUNT on a nullable column skips the null and correctly returns 0. Second, the same fan-out ruins SUM and AVG — a sum of order totals over a joined line-item table multiplies every total by the number of items. If you need both the order and its item total, aggregate the items in a subquery first and join the pre-aggregated result.

Two mistakes are common here. The first is using an INNER JOIN, which silently drops every customer with no orders — and customers with no orders are exactly the ones a retention report cares about. The second is using a correlated subquery in the WHERE clause, which converts the outer join back into an inner join: the WHERE filter discards the null-extended rows, so the customers with no orders disappear anyway.

The shape that handles both is a correlated subquery in the SELECT list, or a window function that ranks each customer's orders:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  placed_on TEXT NOT NULL,
  total INTEGER NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada'),
  (2, 'Grace'),
  (3, 'Katherine');

INSERT INTO orders (id, customer_id, placed_on, total) VALUES
  (1, 1, '2024-01-05', 120),
  (2, 1, '2024-03-11', 80),
  (3, 2, '2024-02-02', 45);

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_on DESC, id DESC) AS rn
  FROM orders o
)
SELECT c.id AS customer_id,
       c.name,
       r.placed_on AS last_order,
       r.total
FROM customers c
LEFT JOIN ranked r
  ON r.customer_id = c.id
 AND r.rn = 1
ORDER BY c.id;

Katherine comes back with nulls in the last two columns, which is the correct answer: she is a customer, she has no orders, and the report should show her as such rather than omit her.

The key detail is that the rn = 1 filter sits in the ON clause, not the WHERE clause. Filters in the WHERE clause run after the join and therefore apply to the null-extended rows too, which is exactly what turns the LEFT JOIN into an INNER JOIN. Keeping the filter in ON means unmatched customers survive with nulls.

One more trap: ordering by placed_on alone is not deterministic if two orders share a date, so the window function adds id DESC as a tiebreaker. In a transaction-heavy table you would normally filter to a date window before ranking, because ranking every order a customer ever made is wasteful when you only want the most recent one.