Expert
CTE
Pro Challenge

Canonical Customer Identity from Conflicting Records

Build one canonical identity per identity_key. For name, precedence is CRM > billing > support > legacy; for email, verified values outrank unverified, then billing > CRM > support > legacy. Within equal precedence choose updated_at DESC then record_id DESC. Missing fields never win. Return identity_key, canonical_name and canonical_email. Return columns: identity_key, canonical_name, canonical_email.

Preparing the interactive SQL workspace...

Challenge overview

What you will practice

Build one canonical identity per identity_key. For name, precedence is CRM > billing > support > legacy; for email, verified values outrank unverified, then billing > CRM > support > legacy. Within equal precedence choose updated_at DESC then record_id DESC. Missing fields never win. Return identity_key, canonical_name and canonical_email. Return columns: identity_key, canonical_name, canonical_email.

Difficulty
Expert
SQL concept
CTE