Interview Question Bank · LOWER · TRIM · NULLIF · COALESCE in GROUP BY
2. Clean up UTM sources before counting
EasyMarketingQuestion #170
Concept: LOWER · TRIM · NULLIF · COALESCE in GROUP BYshow
Hand-typed tracking values split one source into many: 'Google', ' google' and 'GOOGLE ' are three groups unless you normalize them. LOWER(TRIM(x)) fixes case and stray spaces, and grouping by that expression merges them. A blank value is an empty string, not NULL, so NULLIF(..., '') turns it into NULL before COALESCE gives every missing value one label.
Example (different data):
SELECT COALESCE(NULLIF(LOWER(TRIM(coupon_code)), ''), '(no code)') AS coupon, COUNT(*) AS orders FROM checkouts GROUP BY 1 ORDER BY orders DESC;
Your task
Larkspur Roasters' landing-page links were built by hand, so utm_source is a mess of capitals, spaces and blanks. The growth team wants sessions and signups per real source.
Return source (utm_source lowercased and trimmed; NULL or blank becomes '(none)'), sessions, signups (sessions with signed_up true) and signup_rate_pct (signups as a % of sessions, rounded to 1 decimal). Sort by sessions descending, then source.
Starting Postgres in your browser…
Checking your access…