List customers with more than 5 orders.

Why HAVING vs WHERE: WHERE filters before aggregation; HAVING after. For 'more than 5 orders,' you must aggregate first—HAVING is correct. Using WHERE with a subquery is an alternative but less clear.

Scalability: (1) Index customer_id, order_id—enables index-only scan for COUNT. (2) For billions of rows: pre-aggregate in a summary table (customer_order_counts) refreshed incrementally. (3) In Spark: groupBy + filter avoids shuffle if you can push predicate—but COUNT requires shuffle.

Cost: Full table scan on 100M orders = expensive. Partition by date, use incremental aggregation, or materialize customer-level metrics.

SELECT customer_id, COUNT(order_id) AS cnt
FROM orders o JOIN customers c ON o.customer_id = c.id
GROUP BY customer_id
HAVING COUNT(order_id) > 5
ORDER BY cnt DESC;