---------------------------------------------------------------------------------------
------------------------------------------------------------------- COLOCACION NETA SDA
---------------------------------------------------------------------------------------
DROP TABLE IF EXISTS d_mdl_vpc_disc.STAGE_HM_COLNETA_DIARIO

CREATE TABLE d_mdl_vpc_disc.STAGE_HM_COLNETA_DIARIO
WITH ( format = 'Parquet', 
         parquet_compression = 'SNAPPY', 
         partitioned_by = ARRAY['p_periodo'],
         external_location= 's3://sagemaker-us-east-1-058528764918/vpc/contactabilidad/athena_2/STAGE_HM_COLNETA_DIARIO/'
       )
AS (
    SELECT CASE WHEN periodo_desembolso > periodo_solicitud THEN date_add('day', -1, date_add('month', +1, CAST(CONCAT(substr(cast(fecha_solicitud_dt as varchar), 1, 7), '-01')  AS DATE))) ELSE fecha_desemb_dt END fecha_seguimiento_desembolso_dt,
           CASE WHEN periodo_desembolso > periodo_solicitud THEN 1 ELSE 0 END  flg_desembolso_posterior_a_campania,
           date_add('day', -1, date_add('month', +1, CAST(CONCAT(substr(cast(fecha_solicitud_dt as varchar), 1, 7), '-01')  AS DATE))) ultimo_dia_mes_solicitud,
           CAST(split_part(CAST(fecha_desemb_dt - fecha_solicitud_dt as varchar),' ',1)  AS INT) demora_desembolso,
           (BB.colocacion_neta * CAST(BB.tea_aprobada_amt AS real)) / 100.0 interes_ganado,
           (BB.colocacion_neta * (coalesce(RR.primariesgo, 8) + coalesce(RR.margen_comercial, 3.5))) / 100.0 utilidad_aproximada,
           RR.tasapiso_real,
           RR.tasapiso_redondeado,
           coalesce(RR.primariesgo, 8) primariesgo,
           coalesce(RR.margen_comercial, 3.5) margen_comercial,
           RR.gasto_operativo,
           RR.costo_fondo_redondeado,
           RR.costo_fondo_real,
           RR.oferta,
           RR.rmaactual_bpe_final,
           RR.rmaactual_consumo,
           RR.cemoferta,
           RR.cempoperativo,
           RR.bucket,
           BB.*
    FROM (
        SELECT numero_documento_val,
               CASE WHEN tipo_colocacion in ('1. Campaña Nuevos') OR tipo_colocacion LIKE '%1.%' THEN 'NUEVOS PRE'
                    WHEN tipo_colocacion in ( '103.Ecosistemas', '5. Campaña Express', '4. EN – Express') 
                         OR tipo_colocacion LIKE '%103.%'
                         OR tipo_colocacion LIKE '%4.%'
                         OR tipo_colocacion LIKE '%5.%' THEN 'NUEVOS AP'
                    WHEN  campana_val like '%Nuevo Pre%' OR campana_val like '%Nuevos Pre%' OR campana_val like '%Nvo Pre%' OR campana_val like '%NvoPre%' THEN 'NUEVOS PRE'
                    WHEN  campana_val like '%Nuevo Apr%' OR campana_val like '%Nuevos Apr%' OR campana_val like '%NVO APR%' OR campana_val like '%NvoApr%'  THEN 'NUEVOS AP'
                    WHEN flg_rectificado_nuevos_ap = 1 THEN 'NUEVOS AP'
                    WHEN flg_rectificado_nuevos_pre = 1 THEN 'NUEVOS PRE'
               ELSE 'X' END campania,
               min(fecha_desemb_dt) fecha_desemb_dt,
               min(fecha_solicitud_dt) fecha_solicitud_dt,
               substr(replace(cast(fecha_desemb_dt as varchar), '-', ''), 1, 6) periodo_desembolso,
               substr(replace(cast(fecha_solicitud_dt as varchar), '-', ''), 1, 6) periodo_solicitud,
               max(CASE WHEN colocacion_neta > 0 then 1 else 0 end) flg_desembolso,
               max(
                   CASE WHEN tipo_colocacion in ('1. Campaña Nuevos', '103.Ecosistemas', '5. Campaña Express', '4. EN – Express') 
                         OR tipo_colocacion LIKE '%103.%'
                         OR tipo_colocacion LIKE '%4.%'
                         OR tipo_colocacion LIKE '%5.%' THEN 1 
                        WHEN  campana_val like '%RECURRENTE%' THEN 0
                        WHEN campana_val like '%Nuevo Pre%' OR campana_val like '%Nuevo Apr%' THEN 1
                        WHEN  campana_val like '%Nuevos Pre%' OR campana_val like '%Nvo Pre%' OR campana_val like '%NvoPre%' THEN 1
                        WHEN  campana_val like '%Nuevos Apr%' OR campana_val like '%NVO APR%' OR campana_val like '%NvoApr%'  THEN 1
                        WHEN flg_rectificado_nuevos_ap = 1 THEN 1
                        WHEN flg_rectificado_nuevos_pre = 1 THEN 1
                        ELSE 0 END
                ) flg_desembolso_nuevos,
               sum(colocacion_neta) colocacion_neta,
               max(tea_aprobada_amt) tea_aprobada_amt,
               max(fecinformacion_dt) fecinformacion_dt,
               max(flg_rectificado_nuevos_ap) flg_rectificado_nuevos_ap,
               max(flg_rectificado_nuevos_pre) flg_rectificado_nuevos_pre,
               substr(replace(cast(fecha_solicitud_dt as varchar), '-', ''), 1, 6) p_periodo
        FROM (
            select ZZ.*, 
             ROW_NUMBER() OVER(PARTITION BY ZZ.credito_cd ORDER BY ZZ.fecha_solicitud_dt ASC, ZZ.periodo_val ASC, ZZ.fecinformacion_dt ASC) ORDEN
             FROM (
                    SELECT CC.*,
                           CASE WHEN CC.campana_val like '%Recurrente%' and GG.flg_recurrente = 0 AND flg_nuevos_ap = 1 then 1 else 0 end flg_rectificado_nuevos_ap,
                           CASE WHEN CC.campana_val like '%Recurrente%' and GG.flg_recurrente = 0 AND flg_nuevos_pre = 1 then 1 else 0 end flg_rectificado_nuevos_pre
                    FROM e_perm_aws.t_fact_vpc_colnetas CC
                    LEFT JOIN (
                          SELECT gestion , num_ruc_autocompletado_hash, 
                                 MAX(CASE WHEN campania = 'RECURRENTE' then 1 else 0 end) flg_recurrente,
                                 MAX(CASE WHEN campania = 'NUEVOS PRE' then 1 else 0 end) flg_nuevos_pre,
                                 MAX(CASE WHEN campania in ('NUEVOS AP', 'ECOSISTEMAS') then 1 else 0 end) flg_nuevos_ap,
                                 SUM(FLG_GESTIONADO_ESTRICTO) gestion_total,
                                 SUM(flg_ce) ce_total,
                                 SUM(flg_acepta_campana) ac_total
                          FROM  d_mdl_vpc_disc.TMP_CONTACTO_ORDEN_X_PESO_FEEDBACK_2 
                          WHERE CAMPANIA IN ('NUEVOS AP', 'ECOSISTEMAS', 'NUEVOS PRE', 'RECURRENTE')
                          GROUP BY gestion , num_ruc_autocompletado_hash
                    ) GG
                    ON substr(replace(cast(CC.fecha_solicitud_dt as varchar), '-', ''), 1, 6) = GG.gestion
                    AND CC.numero_documento_val = GG.num_ruc_autocompletado_hash
                    WHERE 1 = 1
                    AND CC.colocacion_neta > 0
                    AND CC.tipo_colocacion NOT LIKE '%Cima%'
                    AND CC.campana_val NOT like '%RECURRENTE%'
                    AND (
                        CC.tipo_colocacion in ('1. Campaña Nuevos', '103.Ecosistemas', '5. Campaña Express', '4. EN – Express') OR
                        CC.campana_val like '%Nuevos Pre%' OR CC.campana_val like '%Nvo Pre%' OR CC.campana_val like '%NvoPre%' OR
                        CC.campana_val like '%Nuevos Apr%' OR CC.campana_val like '%NVO APR%' OR CC.campana_val like '%NvoApr%' OR
                        (CC.campana_val like '%Recurrente%' and GG.flg_recurrente = 0 AND GG.flg_nuevos_ap = 1) OR
                        (CC.campana_val like '%Recurrente%' and GG.flg_recurrente = 0 AND GG.flg_nuevos_pre = 1) OR
                        CC.campana_val like '%Nuevo Pre%' OR
                        CC.campana_val like '%Nuevo Apr%' OR
                        CC.tipo_colocacion LIKE '%1.%' OR
                        CC.tipo_colocacion LIKE '%4.%' OR
                        CC.tipo_colocacion LIKE '%5.%' OR
                        CC.tipo_colocacion LIKE '%103.%'
                    )
                    AND CC.fecinformacion_dt in (
                        select fecinformacion_dt from (
                            SELECT periodo_val,
                            max(fecinformacion_dt) fecinformacion_dt
                            FROM e_perm_aws.t_fact_vpc_colnetas
                            GROUP BY periodo_val
                        )
                    )
            ) ZZ
        ) AA
        WHERE AA.ORDEN = 1
        GROUP BY substr(replace(cast(fecha_desemb_dt as varchar), '-', ''), 1, 6),
                 substr(replace(cast(fecha_solicitud_dt as varchar), '-', ''), 1, 6),
                 CASE WHEN tipo_colocacion in ('1. Campaña Nuevos') OR tipo_colocacion LIKE '%1.%' THEN 'NUEVOS PRE'
                    WHEN tipo_colocacion in ( '103.Ecosistemas', '5. Campaña Express', '4. EN – Express') 
                         OR tipo_colocacion LIKE '%103.%'
                         OR tipo_colocacion LIKE '%4.%'
                         OR tipo_colocacion LIKE '%5.%' THEN 'NUEVOS AP'
                    WHEN  campana_val like '%Nuevo Pre%' OR campana_val like '%Nuevos Pre%' OR campana_val like '%Nvo Pre%' OR campana_val like '%NvoPre%' THEN 'NUEVOS PRE'
                    WHEN  campana_val like '%Nuevo Apr%' OR campana_val like '%Nuevos Apr%' OR campana_val like '%NVO APR%' OR campana_val like '%NvoApr%'  THEN 'NUEVOS AP'
                    WHEN flg_rectificado_nuevos_ap = 1 THEN 'NUEVOS AP'
                    WHEN flg_rectificado_nuevos_pre = 1 THEN 'NUEVOS PRE'
                 ELSE 'X' END,
                 numero_documento_val
       ) BB
       LEFT JOIN d_mdl_vpc_disc.HM_RIESGOS_DATA_BPE_AGRUPADO_CAMPANIA RR
       ON BB.periodo_solicitud = RR.periodo_campania
       AND BB.numero_documento_val = RR.num_ruc
       AND BB.campania = RR.campania
)

SELECT * FROM d_mdl_vpc_disc.STAGE_HM_COLNETA_DIARIO 
WHERE periodo_solicitud = '202305'


SELECT periodo_val, tipo_colocacion, campana_val, count(1) conteo, SUM(monto_desemb_neto) monto, COUNT(DISTINCT(numero_documento_val)) conteo_unico
FROM e_perm_aws.t_fact_vpc_colnetas 
--WHERE periodo_val = '202305'--(select max(periodo_val) from e_perm_aws.t_fact_vpc_colnetas )
GROUP BY periodo_val, tipo_colocacion, campana_val
ORDER BY periodo_val DESC, tipo_colocacion, campana_val

SELECT  tipo_colocacion, campana_val, count(1) conteo, SUM(monto_desemb_neto) monto, COUNT(DISTINCT(numero_documento_val)) conteo_unico
FROM e_perm_aws.t_fact_vpc_colnetas 
--WHERE periodo_val = '202303'--(select max(periodo_val) from e_perm_aws.t_fact_vpc_colnetas )
GROUP BY  tipo_colocacion, campana_val
ORDER BY  tipo_colocacion, campana_val


SELECT periodo_solicitud, count(1) conteo, count(distinct(numero_documento_val)) ruc_unicos, sum(colocacion_neta) colocacion_neta, 
      max(fecha_seguimiento_desembolso_dt)
FROM d_mdl_vpc_disc.STAGE_HM_COLNETA_DIARIO  
GROUP BY  periodo_solicitud
ORDER BY  periodo_solicitud desc

SELECT periodo_solicitud, campania,count(1) conteo, count(distinct(numero_documento_val)) ruc_unicos, sum(colocacion_neta) colocacion_neta, 
      max(fecha_seguimiento_desembolso_dt)
FROM d_mdl_vpc_disc.STAGE_HM_COLNETA_DIARIO  
GROUP BY  periodo_solicitud, campania
ORDER BY  periodo_solicitud desc, campania

Add a code snippet to your website: www.paste.org