Combine CRM and ERP using clear source ownership rules.

WITH crm AS(
  WITH ranked AS (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY TRIM(customer_id) ORDER BY updated_at DESC,source_row DESC) AS rn
    FROM crm_people
    WHERE customer_id IS NOT NULL AND TRIM(customer_id)<>''
  )
  SELECT
    TRIM(customer_id) AS customer_id,
    TRIM(name) AS name,
    CASE UPPER(TRIM(gender))
      WHEN 'F' THEN 'Female'
      WHEN 'M' THEN 'Male'
      ELSE NULL
    END AS crm_gender
  FROM ranked
  WHERE rn=1
),erp AS(
  SELECT
    CASE
      WHEN SUBSTR(TRIM(erp_id),1,3)='NAS' THEN SUBSTR(TRIM(erp_id),4)
      ELSE TRIM(erp_id)
    END AS customer_id,
    CASE UPPER(TRIM(gender))
      WHEN 'F' THEN 'Female'
      WHEN 'FEMALE' THEN 'Female'
      WHEN 'M' THEN 'Male'
      WHEN 'MALE' THEN 'Male'
      ELSE NULL
    END AS erp_gender,
    birth_date
  FROM erp_people
),loc AS(
  SELECT
    REPLACE(TRIM(erp_id),'-','') AS customer_id,
    CASE UPPER(TRIM(country_code))
      WHEN 'US' THEN 'United States'
      WHEN 'USA' THEN 'United States'
      WHEN 'GB' THEN 'United Kingdom'
      WHEN 'UK' THEN 'United Kingdom'
      WHEN 'IND' THEN 'India'
      ELSE 'Unknown'
    END AS country
  FROM erp_locations
)
SELECT
  c.customer_id,
  c.name,
  COALESCE(c.crm_gender,e.erp_gender,'Unknown') AS gender,
  COALESCE(l.country,'Unknown') AS country,
  CASE
    WHEN e.birth_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
      AND date(e.birth_date,'+0 days')=e.birth_date
      AND e.birth_date<='2025-04-01'
      THEN e.birth_date
    ELSE NULL
  END AS birth_date
FROM crm c
LEFT JOIN erp e ON e.customer_id=c.customer_id
LEFT JOIN loc l ON l.customer_id=c.customer_id