Understanding deduplication and the concept of freezing

Individual deduplication

Here's an example of deduplication to the 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
    

Managing freeze and sales pressure

Depending on the need, the request will have to manage commercial pressure, as well as freezing for automatic campaigns.