Normalize Messy Campaign Codes

Normalize raw campaign codes by trimming, uppercasing, removing an optional UTM_ prefix, removing spaces, hyphens and underscores, and removing an optional trailing _2025 before separator removal. Return each canonical_code having more than one distinct raw_code, with variant_count and alphabetically joined raw variants. Return columns: canonical_code, variant_count, raw_variants.

Preparing the interactive SQL workspace...

Challenge overview

What you will practice

Normalize raw campaign codes by trimming, uppercasing, removing an optional UTM_ prefix, removing spaces, hyphens and underscores, and removing an optional trailing _2025 before separator removal. Return each canonical_code having more than one distinct raw_code, with variant_count and alphabetically joined raw variants. Return columns: canonical_code, variant_count, raw_variants.

Difficulty: Advanced
SQL concept: CTE