Cities with a loyal, high-spending buyer base

Question

A regional expansion team looks for cities that already have both breadth and depth of demand.

From `purchase_log`, return `city`, the number of distinct buyers as `buyer_count`, and total spend as `total_spend`, keeping only cities that have **at least 3 distinct customers** and **more than 20000

Accepted Answer

Step 1: Aggregate the raw purchases to one row per `city` with `GROUP BY city`. Step 2: Breadth of demand is the number of unique buyers, so use `COUNT(DISTINCT customer_code)` rather than `COUNT(*)` — a single customer can appear on many rows. Step 3: Depth of demand is `SUM(amount)` for the city. Step 4: Both thresholds compare aggregated values, so they belong in `HAVING`, not `WHERE`. `WHERE` is evaluated before grouping and cannot see `SUM`/`COUNT`. Step 5: Combine the two conditions with `AND` so a city must clear both bars.

Cities with a loyal, high-spending buyer base

More HAVING questions

Asked at Walmart