Write a SQL query to join multiple fragmented datasets and resolve conflicting customer records based on incomplete demographic data.
Use the provided customer registry, fragmented profile records, and orders. Match profiles to registry customers using available demographic fields, retain only credible matches, select the strongest profile for each customer, and include unmatched registry customers.
Output
- One row per registry customer.
- Include canonical customer fields, the selected fragment ID and match score, matched fragment count, order count, total spend, and matched source names.
- Sort by
customer_id ascending. Return zero values for customers without orders.
Output details
- Return columns in this order:
customer_id, canonical_name, canonical_email, selected_fragment_id, selected_match_score, matched_fragment_count, matched_sources, order_count, total_spend.
- Require match scores of at least
4, awarding 4 for normalized email, 3 for birth date, 2 for postal code, and 2 for normalized name.
- Normalize emails by trimming and lowercasing, and normalize names by lowercasing and removing non-alphanumeric characters before comparison.
- Break ties by completeness descending, source priority descending, and fragment or customer identifier ascending, selecting one customer per fragment and one fragment per customer.
- Return zero counts and
0.00 total spend for customers without matches or orders, preserving negative amounts and formatting total spend to two decimal places.
