Top Customer Query

Hook: This is the classic 'who brought in the most money?' query — interviewers love it because one tiny SQL mistake can change the winner.

Question: Write a SQL query to find the top customer by total spend.

Answer: First, add up each customer's orders with SUM. Then use a CTE, which means a Common Table Expression — a named temporary result inside one SQL statement — so the totals are easy to read and reuse. Finally, pick the customer whose total matches the highest total, and keep ties if two customers spent the same amount.

Interview-Ready Answer: I’d write a CTE that groups orders by customer and calculates total spend with SUM. Then I’d use a subquery to compare each customer total against MAX(total_spend) from that CTE. That gives me a clear, readable solution, and it also handles ties correctly if multiple customers share the top total.

What this query is really doing

1. Read the raw order rows.
2. Group them by customer.
3. Compute one number per customer, usually SUM(amount).
4. Store that result in a CTE so the logic stays clean.
5. Use a subquery to find the highest total, then return the matching customer or customers.

A subquery is a query inside another query. A CTE is similar, but it has a name, so the SQL reads like a tiny step-by-step program. That is why CTEs are popular in interviews: they make the data flow obvious.

Why use a CTE here?

Without a CTE, the same aggregate can become hard to read, especially when you need to reuse it more than once. A CTE lets you say, 'here are the customer totals,' and then the outer query can focus only on selecting the top result.

Approach	Best for	Watch out
CTE + subquery	Readable logic	Old PostgreSQL may materialize it
ORDER BY ... LIMIT 1	Exactly one row	Hides ties unless you handle them
Window function	Top N or ties	More advanced for beginners

Under the hood, step by step

1. The database scans the order rows.
2. It groups rows by customer_id (and maybe customer name if needed).
3. For each group, it computes the total spend.
4. The CTE stores those totals as a logical result set.
5. The subquery finds the maximum total among those grouped rows.
6. The outer query returns every customer whose total equals that maximum.

Performance and complexity

The main cost is reading and grouping the orders. If there are N order rows, the work is usually close to O(N) with a hash aggregate, or O(N log N) if the engine sorts first. For a table with 10 million orders, the database still has to inspect all relevant rows unless you already have a pre-aggregated summary table.

An index on orders(customer_id) can help some engines group or join more efficiently, but it does not remove the need to count or sum the rows. The biggest win for large systems is often a summary table or materialized view updated on a schedule.

Version note: in PostgreSQL 12+, non-recursive CTEs are often inlined by the optimizer, which means they can behave more like a subquery. In older PostgreSQL versions, CTEs were more likely to be materialized, which could block some optimizations.

Important edge cases

• Ties: If two customers have the same top total, return both unless the business rule says otherwise.
• No orders: Decide whether customers with zero spend should be included. A LEFT JOIN plus COALESCE includes them.
• Wrong metric: Top customer usually means highest total spend, not the single largest order.
• NULL amounts: SUM ignores NULL values, but if every row is null, COALESCE can turn the result into 0.

Memory tip: think of the CTE as a labeled tray of receipts: first sort each customer's receipts into a tray, then ask which tray is heaviest.

Real-world story

Imagine a subscription billing service that sends VIP rewards to the top customer every month. The team needs a query that finds the customer with the highest lifetime spend or monthly spend, depending on the campaign. A CTE makes the query easy for analysts and engineers to review during incident debugging.

Here is the kind of bug that happens when the logic is misunderstood: a developer uses MAX(amount) instead of SUM(amount). One customer places a single big order, another places many smaller orders that add up to more. The system rewards the wrong user, and support starts seeing tickets like 'Why did I lose VIP status even though I spent more overall?'. Logs show the wrong customer ID, the dashboard disagrees with finance numbers, and the mistake is hard to spot until someone checks the aggregation rule.

Why this matters: in production, the difference between 'largest single order' and 'largest total spend' can change promotions, rankings, and revenue reports. This query is small, but the business impact is not.

Follow-up & Tricky Questions

• Follow-up: How would you return the top 3 customers? Use ORDER BY total_spend DESC and a limit of 3, or use a window function like DENSE_RANK() if you want to handle ties cleanly.
• Follow-up: How do you break ties deterministically? Add a second sort key such as customer_id ASC or customer_name ASC, so the result is stable and repeatable.
• Follow-up: How do you include customers with zero spend? Use a LEFT JOIN from customers to orders and wrap the sum with COALESCE(..., 0).
• Follow-up: What if the report is monthly? Add a date filter in the order source, or group by both customer and month before taking the top result.
• Follow-up: Would a window function be simpler? Sometimes yes. A window function can rank totals in one pass, but a CTE is often easier to explain aloud in an interview.
• Tricky: Why is LIMIT 1 not always enough? Because it hides ties and can return an arbitrary row if the sort is not fully deterministic. A tie-aware query is safer for business reports.
• Tricky: Why not use MAX(amount)? Because that answers a different question: it finds the largest single order, not the customer with the largest total spend across all orders.
• Tricky: Can a CTE ever hurt performance? In some older engines, yes, because the optimizer may materialize it. Modern engines often inline non-recursive CTEs, but you should still know the trade-off.

Common Mistakes:

• Using the wrong metric. People use MAX(amount) or COUNT(*) when the question really wants total spend. Fix: confirm whether 'top' means most money, most orders, or latest purchase.
• Ignoring ties. A single LIMIT 1 can hide another customer with the same total. Fix: return all rows matching the maximum, or define a tie-breaker.
• Forgetting zero-order customers. An INNER JOIN removes customers with no orders. Fix: use LEFT JOIN if the report needs the full customer list.
• Making the query hard to read. Nested logic with no naming becomes fragile under pressure. Fix: use a CTE to stage the totals first.

Memory Hook: 'First weigh each customer’s bag of receipts, then pick the heaviest bag.' That is exactly what the CTE plus max comparison is doing.

Cheat Sheet:

• Top customer usually means highest SUM(amount).
• CTE = named temporary result inside one statement.
• Subquery finds the maximum total.
• Use LEFT JOIN + COALESCE for zero-order customers.
• Return all ties unless the business says one winner only.
• Large tables still need a full aggregate scan in most cases.

Practice Tasks:

• Change the sample query to return the top 3 customers by total spend.
• Modify the query so it only counts orders from the last 30 days.
• Rewrite the solution using a window function and compare readability.