Level 2 · Clean and rank the locations in SQL

What you are checking, and why

Customer addresses are typed by customers, so the same place arrives in several spellings. Before you group by anything, measure how bad it is. COUNT(DISTINCT ...) on the raw column against the same count on the cleaned value tells you in one query:

SELECT COUNT(DISTINCT state) AS raw_states,
       COUNT(DISTINCT upper(trim(state))) AS clean_states
FROM customers;

trim removes the stray spaces and upper puts every label in one case. People are matched the same way, on lower(trim(email)), because an email is the closest thing the table has to a person’s identity: the customer_id changes when someone signs up again.

One SQL detail is worth knowing: COUNT(column) skips NULLs, so COUNT(*) - COUNT(city) is the number of blank cities. Those 14 customers cannot be placed in a metro and drop out of step 3.

States first, then metros

Step 2 gives the board the familiar state view. It mixes two sources: the customers table for how many people live in each state, and the order lines for who bought and how much. Each is aggregated on its own and the two are joined on the cleaned state. Joining first and counting later would multiply rows.

Step 3 is the answer to the showroom question. It keeps consumers only, because business accounts order online and would not visit, and it ranks metros, because a showroom serves a city:

WHERE o.status = 'completed'
  AND i.quantity > 0
  AND c.segment = 'consumer'
  AND c.city IS NOT NULL

Both steps put the two years side by side with SUM(CASE WHEN yr = ... THEN revenue END) and compute growth from the unrounded sums.

Reading the result

city	buyers_2025	revenue_2025	growth_pct
NEW YORK	104	96,984.37	−5.9
LOS ANGELES	78	71,631.90	30.7
DALLAS	58	66,362.59	17.2

New York is the largest and Los Angeles the fastest growing of the leaders. The state table would have pointed at California as a whole, and its top-line growth of 0.3% would have hidden that Los Angeles is growing fast.

Pitfalls

• Cleaning in GROUP BY but not in the SELECT (or the other way round) gives an error or splits groups again. Clean once, in a CTE, and use the cleaned column everywhere.
• upper(trim(city)) returns NEW YORK. That is fine for grouping; tidy the case for the slide.
• LIMIT without ORDER BY returns ten arbitrary states.