---------------------------------------------------------------------------------------
------------------------------------------------------------------- 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, campaniaAdd a code snippet to your website: www.paste.org