Level 6 · Boss: customer base profile

This boss combines the chapter’s tools into a profile marketing can plan with.

Step 1: signups by month and segment

This is a small pivot: months down the side, segments across the top. DATE_TRUNC builds the month key and conditional aggregation splits the count:

SUM(CASE WHEN segment = 'business' THEN 1 ELSE 0 END) AS business_signups

signup_date is already a DATE, so the half-open range compares cleanly. Including total_signups lets the reader verify that the two segment columns add up, a small courtesy that builds trust.

Step 2: email domains

The domain is everything after the @. DuckDB and Postgres have SPLIT_PART(s, delimiter, n), which splits a string and returns the nth piece (1-based):

SPLIT_PART(LOWER(TRIM(email)), '@', 2) AS domain

Clean first, then split: ' Owen@ICloud.com' and 'owen@icloud.com' must land in the same group. Dialects vary a lot here:

database	domain of an email
DuckDB, Postgres, Snowflake	SPLIT_PART(email, '@', 2)
BigQuery	SPLIT(email, '@')[SAFE_OFFSET(1)] (0-based)
MySQL	SUBSTRING_INDEX(email, '@', -1)
Any of them	SUBSTRING(email FROM POSITION('@' IN email) + 1)

A count of customer rows by domain is a first look, not a final answer: duplicate customer records inflate it slightly. You will deduplicate customers properly in chapter 7.

Step 3: labeling unknowns

COALESCE(a, b, ...) returns its first non-NULL argument. It is the standard way to give a NULL a label in a report:

COALESCE(referral_source, 'unknown') AS referral_source

Grouping by the coalesced value turns the NULL group into a readable “unknown” row. That row matters: if a third of customers have no recorded source, the attribution data has a gap, and Jordan should know before splitting a budget by source. Do not drop it with a WHERE.

Use COALESCE for labels and for real defaults (a missing discount really is 0). Do not use it to hide unknown numbers: COALESCE(unit_cost, 0) would make unknown-cost products look 100% profitable.

Dialect note

COALESCE is standard and works everywhere. You will also see IFNULL(a, b) in MySQL and BigQuery, NVL in Oracle and Snowflake, and ISNULL in SQL Server. They take exactly two arguments; COALESCE takes any number.