Paste code

DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_ADEX_PRICING

CREATE TABLE d_mdl_vpc_disc.HM_ADEX_PRICING
WITH ( format = &#039;Parquet&#039;, 
         parquet_compression = &#039;SNAPPY&#039;, 
         partitioned_by = ARRAY[&#039;p_periodo&#039;],
         external_location= &#039;s3://sagemaker-us-east-1-058528764918/vpc/pricingbpe/athenav2/HM_ADEX_PRICING/&#039;
       )
AS (
SELECT CAST(periodo_val as varchar) periodo_adex, ruc_cd num_ruc, 
       substr(replace(substr(cast(date_add(&#039;month&#039;, +2, date_parse(CAST(periodo_val as varchar) ,&#039;%Y%m&#039;)) as varchar), 1, 10), &#039;-&#039;, &#039;&#039;), 1, 6) periodo_ejecucion,
       substr(replace(substr(cast(date_add(&#039;month&#039;, +3, date_parse(CAST(periodo_val as varchar) ,&#039;%Y%m&#039;)) as varchar), 1, 10), &#039;-&#039;, &#039;&#039;), 1, 6) periodo_campania,
       SUM(valor_mto) monto_adex,
       SUM(CASE WHEN tipo_dsc = &#039;EXP&#039; THEN valor_mto else 0 END) monto_exportacion,
       SUM(CASE WHEN tipo_dsc = &#039;IMP&#039; THEN valor_mto else 0 END) monto_importtacion, 
       SUM(CASE WHEN tipo_dsc = &#039;EXP&#039; AND continente_dsc IN (&#039;AFRICA&#039;, &#039;ASIA&#039;, &#039;OCEANIA&#039;, &#039;EUROPA&#039;) THEN valor_mto else 0 END) monto_exporttacion_otro_continente,
       SUM(CASE WHEN tipo_dsc = &#039;IMP&#039; AND continente_dsc IN (&#039;AFRICA&#039;, &#039;ASIA&#039;, &#039;OCEANIA&#039;, &#039;EUROPA&#039;) THEN valor_mto else 0 END) monto_importtacion_otro_continente,  
       SUM(CASE WHEN tipo_dsc = &#039;EXP&#039; AND continente_dsc LIKE &#039;%AMERICA%&#039; THEN valor_mto else 0 END) monto_exporttacion_america,
       SUM(CASE WHEN tipo_dsc = &#039;IMP&#039; AND continente_dsc LIKE &#039;%AMERICA%&#039; THEN valor_mto else 0 END) monto_importtacion_america,
       periodo_val p_periodo
FROM e_perm_aws.mst_vpc_adex
WHERE ruc_cd IN (select num_ruc from d_mdl_vpc_disc.STAGE_HM_BASE_CAMPANIA_PRICING)
GROUP BY periodo_val, ruc_cd
)