Untitled

SELECT  CLIE.*,
        MAX(CLIE_DETALLE.pol_des_canal),
        MAX(CLIE_DETALLE.pol_des_subcanal),
        MAX(CLIE_DETALLE.des_producto)
from (
  SELECT id_cliente_persona,
          nombres,
          ape_paterno,
          ape_materno,
          des_segmentacion_growth,
          tip_documento,
          num_documento,
          ind_consentimiento_comercial 
  FROM `rs-shr-al-analyticsz-prj-ebc1.anl_persona.cliente_persona`
  WHERE periodo="2023-04-01"
) CLIE
INNER JOIN `rs-shr-al-analyticsz-prj-ebc1.anl_persona.cliente_persona_detalle` CLIE_DETALLE
ON CLIE.id_cliente_persona = CLIE_DETALLE.id_cliente_persona
AND CLIE_DETALLE.periodo="2023-04-01"
AND CLIE.ind_consentimiento_comercial ="NO"
AND CLIE.des_segmentacion_growth in ("CHANGE - AGENTS","PROSPEROUS")

GROUP BY CLIE.id_cliente_persona,
          CLIE.nombres, 
          CLIE.ape_paterno,
          CLIE.ape_materno,
          CLIE.tip_documento,
          CLIE.des_segmentacion_growth,
          CLIE.num_documento,
          CLIE.ind_consentimiento_comercial