Quick answer: Use WHERE to choose the individual orders that count, then HAVING SUM(amount_usd) > 100 to select customers whose combined qualifying spend exceeds $100. WHERE amount_usd > 100 selects large individual orders and can exclude customers who qualify through several smaller purchases.

By Prakhar Shrivastava · October 22, 2026

Two $60 purchases should qualify

Suppose a fictional report asks for customers with more than $100 in completed-order spend during a fixed reporting period. US-A has two completed $60 orders. UK-B has one completed $150 order. DE-C has a completed $40 order and a canceled $200 order. All amounts are already normalized to USD, customer IDs are globally unique, and the source has one row per order.

The correct qualifying customers are US-A at $120 and UK-B at $150. DE-C does not qualify: canceled orders do not count under this exercise’s metric definition. This is an educational rule, not a claim about any employer’s reporting policy.

The row filter answers a different question

Run this complete fixture and query. The syntax is PostgreSQL-compatible; the expected results were checked in SQLite.

WITH orders(order_id, customer_id, market, amount_usd, status) AS (
 VALUES (1,'US-A','US',60,'completed'),
        (2,'US-A','US',60,'completed'),
        (3,'UK-B','UK',150,'completed'),
        (4,'DE-C','DE',40,'completed'),
        (5,'DE-C','DE',200,'canceled')
)
SELECT customer_id, SUM(amount_usd) AS spend_usd
FROM orders
WHERE status = 'completed' AND amount_usd > 100
GROUP BY customer_id
ORDER BY customer_id;

Result: only UK-B, with $150. Both US-A orders fail the individual-order threshold and disappear before grouping. This query is valid SQL, but it answers “spend from completed orders individually above $100,” not “customers with combined completed spend above $100.”

Keep order eligibility in WHERE and customer eligibility in HAVING

Keep the same orders CTE and replace the final query with:

SELECT customer_id, SUM(amount_usd) AS spend_usd
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount_usd) > 100
ORDER BY customer_id;

customer_id  spend_usd
UK-B                150
US-A                120

The completed-order filter establishes the input population. Grouping then combines all qualifying orders for each customer. HAVING evaluates the resulting customer total. The two returned totals sum to $270; total completed-order revenue is $310 because DE-C’s $40 is intentionally excluded from this customer segment.

Do not remove the status filter when moving the amount threshold. Without it, DE-C incorrectly qualifies at $240. A query can use both WHERE and HAVING because they express conditions at different stages.

Does exactly $100 count?

“More than $100” uses > 100. “At least $100” uses >= 100. Make the boundary explicit in the metric definition. In this fixture, changing the threshold to > 120 excludes US-A, while >= 120 retains it. Test a boundary customer rather than assuming wording is interchangeable.

Apply the same reporting window to every customer

For a real table, add the agreed date-window condition alongside status in WHERE. Define which timestamp counts, its timezone, and the currency basis before filtering. UK and German orders should not be compared to a USD threshold using unconverted GBP or EUR amounts. The synthetic fixture assumes these preparations have already been completed.

If customer IDs are reused by separate source systems, group by the documented composite customer key. Grouping only by a locally unique ID can merge unrelated people. If order rows are expanded by a join, repair the grain before summing; moving a condition to HAVING does not fix duplicate amounts.

Handle incomplete amounts explicitly

The fixture has no missing amounts or refunds. In production, SUM skips null amounts, so the result may be a known subtotal. Report missing-value coverage or exclude incomplete groups under a documented policy. Do not silently replace unknown amounts with zero.

If refunds are represented as negative rows, agree whether they reduce the period’s qualifying spend and how their dates relate to the original purchase. Filtering negative values out merely to make a customer pass the threshold changes the metric.

Explain the result before using it

A concise interview explanation is: “WHERE selects completed orders in the reporting window. GROUP BY produces one total per customer. HAVING selects the totals above the threshold. I will test multiple small orders, canceled orders and the exact boundary.”

The official PostgreSQL GROUP BY and HAVING documentation describes this distinction. The customer data and expected totals here are original worked examples.

Continue with date-window boundaries, duplicate joins and revenue, or missing amounts versus zero.