Connaitre la déduplication et la notion du gel

Déduplication des individus

Voici un exemple de déduplication au customer_id, business_unit_brand_id  & business_unit_country_code.

  /****** Type here HQL query. ******/

  WITH filtered_cu AS (
  SELECT customer_id, email, business_unit_brand_id, business_unit_country_code,
  ROW_NUMBER() OVER (PARTITION BY customer_id, business_unit_brand_id, business_unit_country_code
  ORDER BY last_interaction_date DESC) as rn
  FROM client_user
  WHERE is_master = 1
  ),

  filtered_co AS(
  SELECT customer_id, product_amount_with_tax, business_unit_brand_id, business_unit_country_code
  FROM client_order
  WHERE order_date BETWEEN '2023-11-12' AND '2023-11-15'
  AND product_amount_with_tax > 75
  )

  SELECT email, SUM(co.product_amount_with_tax) as sum_product_amount_with_tax
  FROM filtered_cu cu
  JOIN filtered_co co
  ON cu.customer_id = co.customer_id
  AND cu.business_unit_brand_id = co.business_unit_brand_id
  AND cu.business_unit_country_code = co.business_unit_country_code
  WHERE cu.rn = 1
  GROUP BY email
  ORDER BY sum_product_amount_with_tax DESC
    

Gérer le gel et la pression commerciale 

En fonction du besoin, la requête devra gérer la pression commerciale, ainsi que le gel pour les campagnes automatiques.