Untitled

SELECT canal, subcanal, producto, count(*) as cantidad
FROM (

        SELECT  CLIE.*,
        MAX(CLIE_DETALLE.pol_des_canal) as canal,
        MAX(CLIE_DETALLE.pol_des_subcanal) as subcanal,
        MAX(CLIE_DETALLE.des_producto) as 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 

)

GROUP BY canal, subcanal, producto