Drill 2 of 12 in Marketing questions
0

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…