Customer Loyalty Tier

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH reporting_dates AS (
  SELECT calendar_date AS snapshot_date
  FROM `your-project.reference.calendar_date`
  WHERE calendar_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 89 DAY) AND CURRENT_DATE()
),
source_rows AS (
  SELECT d.snapshot_date, NULLIF(TRIM(CAST(h.customer_analytics_key AS STRING)), '') AS customer_partition_key,
    h.identity_status, h.program_code, h.member_status, h.native_tier_code, h.is_primary_program,
    h.primary_program_priority, h.effective_from_date, h.source_updated_at, h.membership_record_id
  FROM reporting_dates d
  JOIN `your-project.crm.loyalty_membership_history` h
    ON d.snapshot_date >= h.effective_from_date
   AND (h.effective_to_date IS NULL OR d.snapshot_date < h.effective_to_date)
  WHERE NULLIF(TRIM(CAST(h.customer_analytics_key AS STRING)), '') IS NOT NULL
),
primary_membership_candidates AS (
  SELECT s.*, ROW_NUMBER() OVER (
    PARTITION BY snapshot_date, customer_partition_key
    ORDER BY CASE WHEN COALESCE(is_primary_program, FALSE) THEN 0 ELSE 1 END,
      COALESCE(primary_program_priority, 2147483647), effective_from_date DESC, source_updated_at DESC, membership_record_id DESC
  ) AS primary_rank
  FROM source_rows s
),
normalized AS (
  SELECT snapshot_date, customer_partition_key,
    REGEXP_REPLACE(LOWER(TRIM(identity_status)), r'[ -]+', '_') AS identity_status,
    REGEXP_REPLACE(LOWER(TRIM(program_code)), r'[ -]+', '_') AS program_code,
    REGEXP_REPLACE(LOWER(TRIM(member_status)), r'[ -]+', '_') AS member_status,
    REGEXP_REPLACE(LOWER(TRIM(native_tier_code)), r'[ -]+', '_') AS native_tier_code
  FROM primary_membership_candidates
  WHERE primary_rank = 1
),
normalized_reference AS (
  SELECT REGEXP_REPLACE(LOWER(TRIM(program_code)), r'[ -]+', '_') AS program_code,
    REGEXP_REPLACE(LOWER(TRIM(source_tier_code)), r'[ -]+', '_') AS source_tier_code,
    REGEXP_REPLACE(LOWER(TRIM(approved_native_tier_code)), r'[ -]+', '_') AS approved_native_tier_code,
    effective_from_date, effective_to_date, source_updated_at, mapping_id
  FROM `your-project.reference.loyalty_native_tier_reference`
),
mapping_candidates AS (
  SELECT n.*, r.approved_native_tier_code, ROW_NUMBER() OVER (
    PARTITION BY n.snapshot_date, n.customer_partition_key
    ORDER BY r.effective_from_date DESC, r.source_updated_at DESC, r.mapping_id DESC
  ) AS mapping_rank
  FROM normalized n
  LEFT JOIN normalized_reference r
    ON n.program_code = r.program_code
   AND n.native_tier_code = r.source_tier_code
   AND n.snapshot_date >= r.effective_from_date
   AND (r.effective_to_date IS NULL OR n.snapshot_date < r.effective_to_date)
),
classified AS (
  SELECT snapshot_date, program_code, member_status,
    CASE
      WHEN identity_status = 'anonymous' THEN 'not_available'
      WHEN member_status IN ('not_enrolled', 'non_member') THEN 'not_enrolled'
      WHEN member_status IN ('unknown', 'unresolved') THEN 'unknown'
      WHEN NULLIF(native_tier_code, '') IS NULL THEN 'unknown'
      WHEN approved_native_tier_code IS NULL THEN 'unmapped'
      ELSE approved_native_tier_code
    END AS customer_loyalty_tier
  FROM mapping_candidates
  WHERE mapping_rank = 1
)
SELECT snapshot_date, program_code, member_status, customer_loyalty_tier, COUNT(*) AS customer_count
FROM classified
GROUP BY snapshot_date, program_code, member_status, customer_loyalty_tier
ORDER BY snapshot_date, program_code, customer_loyalty_tier;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH reporting_dates AS (
  SELECT calendar_date AS snapshot_date
  FROM enterprise_calendar
  WHERE calendar_date BETWEEN CURRENT_DATE - INTERVAL '89' DAY AND CURRENT_DATE
),
source_rows AS (
  SELECT d.snapshot_date, NULLIF(TRIM(CAST(h.customer_analytics_key AS STRING)), '') AS customer_partition_key,
    h.identity_status, h.program_code, h.member_status, h.native_tier_code, h.is_primary_program,
    h.primary_program_priority, h.effective_from_date, h.source_updated_at, h.membership_record_id
  FROM reporting_dates d
  JOIN loyalty_membership_history h
    ON d.snapshot_date >= h.effective_from_date
   AND (h.effective_to_date IS NULL OR d.snapshot_date < h.effective_to_date)
  WHERE NULLIF(TRIM(CAST(h.customer_analytics_key AS STRING)), '') IS NOT NULL
),
primary_membership_candidates AS (
  SELECT s.*, ROW_NUMBER() OVER (
    PARTITION BY snapshot_date, customer_partition_key
    ORDER BY CASE WHEN COALESCE(is_primary_program, FALSE) THEN 0 ELSE 1 END,
      COALESCE(primary_program_priority, 2147483647), effective_from_date DESC, source_updated_at DESC, membership_record_id DESC
  ) AS primary_rank
  FROM source_rows s
),
normalized AS (
  SELECT snapshot_date, customer_partition_key,
    REGEXP_REPLACE(LOWER(TRIM(identity_status)), '[ -]+', '_') AS identity_status,
    REGEXP_REPLACE(LOWER(TRIM(program_code)), '[ -]+', '_') AS program_code,
    REGEXP_REPLACE(LOWER(TRIM(member_status)), '[ -]+', '_') AS member_status,
    REGEXP_REPLACE(LOWER(TRIM(native_tier_code)), '[ -]+', '_') AS native_tier_code
  FROM primary_membership_candidates
  WHERE primary_rank = 1
),
normalized_reference AS (
  SELECT REGEXP_REPLACE(LOWER(TRIM(program_code)), '[ -]+', '_') AS program_code,
    REGEXP_REPLACE(LOWER(TRIM(source_tier_code)), '[ -]+', '_') AS source_tier_code,
    REGEXP_REPLACE(LOWER(TRIM(approved_native_tier_code)), '[ -]+', '_') AS approved_native_tier_code,
    effective_from_date, effective_to_date, source_updated_at, mapping_id
  FROM loyalty_native_tier_reference
),
mapping_candidates AS (
  SELECT n.*, r.approved_native_tier_code, ROW_NUMBER() OVER (
    PARTITION BY n.snapshot_date, n.customer_partition_key
    ORDER BY r.effective_from_date DESC, r.source_updated_at DESC, r.mapping_id DESC
  ) AS mapping_rank
  FROM normalized n
  LEFT JOIN normalized_reference r
    ON n.program_code = r.program_code
   AND n.native_tier_code = r.source_tier_code
   AND n.snapshot_date >= r.effective_from_date
   AND (r.effective_to_date IS NULL OR n.snapshot_date < r.effective_to_date)
),
classified AS (
  SELECT snapshot_date, program_code, member_status,
    CASE
      WHEN identity_status = 'anonymous' THEN 'not_available'
      WHEN member_status IN ('not_enrolled', 'non_member') THEN 'not_enrolled'
      WHEN member_status IN ('unknown', 'unresolved') THEN 'unknown'
      WHEN native_tier_code IS NULL OR native_tier_code = '' THEN 'unknown'
      WHEN approved_native_tier_code IS NULL THEN 'unmapped'
      ELSE approved_native_tier_code
    END AS customer_loyalty_tier
  FROM mapping_candidates
  WHERE mapping_rank = 1
)
SELECT snapshot_date, program_code, member_status, customer_loyalty_tier, COUNT(*) AS customer_count
FROM classified
GROUP BY snapshot_date, program_code, member_status, customer_loyalty_tier
ORDER BY snapshot_date, program_code, customer_loyalty_tier;