Level 1 · One key per location

Chapter 6 cleaned one column: UPPER(TRIM(state)). Real reports usually group on several messy columns at once, so you need a key: one standardized value that identifies the thing you are counting, built from cleaned parts.

Building a key

A key has three ingredients: clean each part, pick one case, and join the parts with a separator that cannot appear inside them.

CONCAT(UPPER(TRIM(state)), '-', LOWER(TRIM(city)))   -- 'NY-new york'

TRIM drops leading and trailing spaces, UPPER and LOWER fix casing, and CONCAT glues the pieces. Lowercase is a fine choice for the city part: it is a key, not a display label. DuckDB has no INITCAP, so pretty title case is a job for the report layer.

Why include the state? Because Harbor & Pine has customers in both Portland, Oregon and Portland, Maine. Keyed on city alone they would merge into one location.

Measuring the mess

Count the raw values inside each clean group to see how much the cleaning did:

SELECT
  CONCAT(UPPER(TRIM(state)), '-', LOWER(TRIM(city))) AS location_key,
  COUNT(*) AS customers,
  COUNT(DISTINCT city || '|' || state) AS raw_spellings
FROM customers
WHERE city IS NOT NULL
GROUP BY 1;

On this data the 2,004 customers with a city have 168 distinct raw city and state pairs, which collapse into 62 locations. NY-new york has 181 customers spread over 5 spellings, and TX-houston has 8. The 14 customers with a NULL city are left out on purpose: a NULL is missing data, not a spelling, and it gets its own treatment in level 3.

Common mistakes

• No separator. COUNT(DISTINCT city || state) returns 163 instead of 168, because 'Houston' || ' TX' and 'Houston ' || 'TX' produce the same string. Put a character like | between parts, or count the pair directly with COUNT(DISTINCT (city, state)).
• Cleaning in SELECT, grouping raw. If you GROUP BY city, state and only clean in SELECT, you get several rows that display the same key, each with a partial count.
• Too much cleaning. Removing every space would turn “San Jose” into “sanjose”. Trim the ends, keep the inside.

On the job

Keep a list of the raw spellings you collapsed and send it to whoever owns the CRM form. Fixing the input (a dropdown instead of free text) beats cleaning the same mess in every query forever. In an interview, “how would you count customers per city?” is a test of whether you check the text first.

In SQL Server

TRIM, UPPER, LOWER and CONCAT exist under the same names (TRIM since SQL Server 2017; older code uses LTRIM(RTRIM(city))). Use + or CONCAT to join strings. Watch LEN: it ignores trailing spaces, so LEN('Boston ') is 6, while DATALENGTH counts bytes.

SELECT CONCAT(UPPER(TRIM(state)), '-', LOWER(TRIM(city))) AS location_key, COUNT(*) AS customers
FROM customers WHERE city IS NOT NULL
GROUP BY CONCAT(UPPER(TRIM(state)), '-', LOWER(TRIM(city)));