Untitled

EXPORT DATA OPTIONS(
  uri='gs://rs_nonprod_stg_inp_sftp_raw_generics/data_entries/de__tribudata/de__gobierno/*.csv',
  format='CSV',
  overwrite=true,
  header=true,
  field_delimiter=';') AS

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