-- SET @pbi_datetime = '2018-01-01 10:00:10'

-- POPULATE DIM_CUSTOMER_MINI
INSERT INTO `dim_customer_mini`
(age, gender_code, gender_name, group_id, group_code, pk_dim_customer_mini, inserted_at)
select a.age,
  b.gender as gender_code,
  CASE b.gender
      WHEN 1 THEN 'Male'
      WHEN 2 THEN 'Female'
      ELSE 'Male or Female'
  END as gender_name,
  c.customer_group_id, c.customer_group_code,
  c.customer_group_id*100000+a.age*100+b.gender as pk_dim_customer_mini,
   @db_date_time as inserted_at
  from bim_stg_age a
join (
      select DISTINCT gender from bim_stg_customer where gender <> 0
        UNION
      select 0
     ) b
join (
       select DISTINCT customer_group_id, customer_group_code from bim_stg_customer_group
     ) c
LEFT JOIN `dim_customer_mini` dim
on dim.pk_dim_customer_mini = c.customer_group_id*100000+a.age*100+b.gender
WHERE dim.pk_dim_customer_mini iS NULL;

-- {{IF_ANALYZE}}dim_customer_mini{{/IF_ANALYZE}}