Paste code

CREATE TABLE d_mdl_vpc_disc.T1_SALDO_MERCADO_RCC
WITH ( format = &#039;Parquet&#039;, 
	   parquet_compression = &#039;SNAPPY&#039;, 
	   partitioned_by = ARRAY[&#039;PERIODO&#039;], 
	   external_location= &#039;s3://sagemaker-us-east-1-058528764918/vpc/propension/athena/T1_SALDO_MERCADO_RCC/&#039;
	 )
AS (
SELECT 
	  A.cod_sbs_val AS CODSBS,
	  A.producto_rcc_desc AS PRODUCTO,
	  A.situacion_rcc_desc AS SITUACION,
	  A.empresa_cd AS COD_EMPRESA,
	  CASE WHEN coalesce(A.tipoempresafinanc_desc,&#039;&#039;) =&#039;BANCOS&#039; AND coalesce(A.estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN &#039;BANCOS&#039; ELSE &#039;CAJAS & FINANC.&#039; END AS TIPO_ENTIDAD,
	  CASE WHEN coalesce(A.tipoempresafinanc_desc,&#039;&#039;) =&#039;BANCOS&#039; AND coalesce(A.estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN
	  A.nomcortoempresafinanc_desc ELSE &#039;CAJAS & FINANC.&#039; END AS BANCO,
	  A.tipo_producto_rcc_desc TIPO_PRODUCTO,
	  SUM(coalesce(A.saldo_amt, 0)) MTO_SALDO,
      MAX(coalesce(A.flg_lin_mercado, &#039;N&#039;)) flg_lin_mercado,
	  MAX(coalesce(A.condicion_val, 0)) MAX_DIAS_ATRASO,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, &#039;8&#039;) in (&#039;0&#039;, &#039;8&#039;) then 1 else 0 END) FLG_TIENE_CLASIF_NORMAL,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, &#039;8&#039;) = &#039;1&#039; then 1 else 0 END) FLG_TIENE_CLASIF_CPP,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, &#039;8&#039;) = &#039;2&#039; then 1 else 0 END) FLG_TIENE_CLASIF_DEFICIENTE,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, &#039;8&#039;) = &#039;3&#039; then 1 else 0 END) FLG_TIENE_CLASIF_DUDOSO,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, &#039;8&#039;) not in (&#039;0&#039;,&#039;1&#039;, &#039;2&#039;, &#039;3&#039;, &#039;8&#039;) then 1 else 0 END) FLG_TIENE_CLASIF_PERDIDA,
      CASE WHEN cod_sbs_val in (select cod_sbs_val from e_perm_aws.t_vpc_segmentacion_merc where banca_fin_val = &#039;BPE&#039; or banca_mes_val = &#039;BPE&#039;) THEN 1 ELSE 0 END FLG_BPE_TIMELINE,
      CASE WHEN cod_sbs_val in (select cod_sbs_val from e_perm_aws.t_vpc_segmentacion_merc where banca_fin_val = &#039;BE&#039; or banca_mes_val = &#039;BE&#039;) THEN 1 ELSE 0 END FLG_BE_TIMELINE,
      CASE WHEN cod_sbs_val in (select cod_sbs_val from e_perm_aws.t_vpc_segmentacion_merc where banca_fin_val = &#039;BC&#039; or banca_mes_val = &#039;BC&#039;) THEN 1 ELSE 0 END FLG_BC_TIMELINE,
      CASE WHEN cod_sbs_val in (select cod_sbs_val from e_perm_aws.t_vpc_segmentacion_merc where banca_fin_val = &#039;MICRO&#039; or banca_mes_val = &#039;MICRO&#039;) THEN 1 ELSE 0 END FLG_MICRO_TIMELINE,
	  periodo_val AS PERIODO
   FROM e_perm_aws.T_VPC_FACT_SALDOS_RCC A
   LEFT JOIN e_perm_aws.t_dim_vpc_cta_ctble_rcc B
   ON A.Cuenta_RCC_Val = B.Cuenta_Ctble_RCC_Cd
   WHERE 1 = 1
   AND date_parse(cast(periodo_val as varchar),&#039;%Y%m&#039;) >= (SELECT date_add(&#039;month&#039;, -18, date_parse(cast(MAX(periodo_val) as varchar),&#039;%Y%m&#039;)) FROM e_perm_aws.T_VPC_FACT_SALDOS_RCC)
   AND A.tipo_producto_rcc_desc IN (
	 &#039;COL. DIRECTAS&#039;,&#039;REACTIVA&#039;,&#039;COL. INDIRECTAS&#039;,&#039;FAE&#039;,&#039;INMOBILIARIO&#039;, &#039;CREDITOS CASTIGADOS&#039;, &#039;GARANTIA_FAE&#039;, &#039;GARANTIA_REACTIVA&#039;, &#039;GARANTIAS&#039;, &#039;REPROGRAMADOS&#039;
   )
   AND 
   (
	  (
		  A.flg_lin_mercado = &#039;S&#039; AND
		  (
			A.producto_rcc_desc NOT IN (&#039;OTROS&#039;) 
			OR 
			(
			  A.producto_rcc_desc =&#039;OTROS&#039; AND 
			  B.Cuenta_Ctble_RCD_Desc IN (&#039;FINANCIACION DE PROYECTOS&#039;)
			)
		  )
	  )					
	  OR A.tipo_producto_rcc_desc IN (&#039;REACTIVA&#039;,&#039;FAE&#039;,&#039;INMOBILIARIO&#039;, &#039;CREDITOS CASTIGADOS&#039;, &#039;GARANTIA_FAE&#039;, &#039;GARANTIA_REACTIVA&#039;, &#039;GARANTIAS&#039;, &#039;REPROGRAMADOS&#039;)
  )
  GROUP BY 
	  A.periodo_val,
	  A.cod_sbs_val,
	  A.producto_rcc_desc,
	  A.situacion_rcc_desc,
	  A.empresa_cd,
	  CASE WHEN coalesce(A.tipoempresafinanc_desc,&#039;&#039;) =&#039;BANCOS&#039; AND coalesce(A.estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN &#039;BANCOS&#039; ELSE &#039;CAJAS & FINANC.&#039; END,
	  CASE WHEN coalesce(A.tipoempresafinanc_desc,&#039;&#039;) =&#039;BANCOS&#039; AND coalesce(A.estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN
	  A.nomcortoempresafinanc_desc ELSE &#039;CAJAS & FINANC.&#039; END,
	  A.tipo_producto_rcc_desc
  
)