Level 8 · Boss: a clean customer view

Cleaning pays off when one cleaned table feeds many reports. This boss builds a clean customer view once, with every rule from the chapter, and answers three questions from it.

The clean customer view

Write the rules down before the SQL:

• A person is a cleaned email, LOWER(TRIM(email)).
• The surviving record is the earliest signup, ties broken by the lowest customer_id.
• State is UPPER(TRIM(state)). Referral source is COALESCE(referral_source, 'unknown').
• Revenue comes only from completed orders and lines with a positive quantity.

WITH clean_customers AS (
  SELECT customer_id, UPPER(TRIM(state)) AS state
  FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY LOWER(TRIM(email)) ORDER BY signup_date, customer_id) AS rn
    FROM customers
  ) AS ranked
  WHERE rn = 1
)

Every step repeats this CTE. On the job it would be a saved view, and every dashboard would select from it.

Step 1: how much did cleaning change?

Before reporting, show the effect of the rules. Of 1,732 consumer rows, 1,716 are distinct people. Of 286 business rows, 284. Reporting this next to the numbers makes the cleaning auditable.

Step 2: revenue by clean state

Aggregate revenue per customer first, then join: one row per customer on both sides, so no fan-out. With raw state values, California would appear as CA, ' CA', 'ca' and 'CA ', with 234, 2, 2 and 2 buyers. After cleaning it is one row, the top state with 240 buying customers.

Step 3: which sources turn into buyers?

people counts everyone in the clean view; buyers counts those with at least one completed order. A LEFT JOIN keeps the people who never bought; COUNT(b.customer_id) skips their NULLs. The unknown group is still the biggest at 648 people, down from 666, because the 18 duplicate records all had no referral source. NULLIF protects the division in case a group is ever empty.

Common mistakes

• Inconsistent rules between steps. If step 2 uses the raw state, its total no longer matches step 1.
• Joining orders before deduping. It works here only because duplicates have no orders. In real data, re-point the dropped ids to the kept id first.
• INNER JOIN for buyers. It removes non-buyers, so every source shows 100%.

In SQL Server

The same query works with ISNULL or COALESCE and TOP 10 instead of LIMIT 10 (SELECT TOP 10 c.state, ... ORDER BY net_revenue DESC). Save the CTE as a view with CREATE VIEW clean_customers AS SELECT .... A view cannot hold the ORDER BY without TOP.