Paste code

table_ =&#039;HM_BASE_DESPLIEGUE_2&#039;
apply_create(
    table=table_, 
    path=&#039;{}vpc/aceptacion/athena_nuevomes/{}/&#039;.format(path_, table_), 
    llave=&#039;p_periodo&#039;, 
    query="""
  SELECT periodo_campania,  num_ruc,
               MAX(CASE WHEN upper(producto) in (&#039;NUEVOS APPAS&#039;, &#039;NUEVOS APCONSUMO&#039;, &#039;NUEVOS AP&#039;, &#039;NUEVOS AP SUPER_AGIL&#039;, &#039;PAGARE CERRADO&#039;) 
                   AND upper(subproducto) NOT LIKE &#039;%ECOSISTEMAS%&#039; 
                   AND upper(subproducto) NOT LIKE &#039;%IMPULSO%&#039; then 1 else 0 end) flg_nuevos_ap,
               MAX(CASE WHEN upper(producto) in (&#039;NUEVOS PRE&#039;) then 1 else 0 end) flg_nuevos_pre,
               MAX(CASE WHEN upper(producto) in (&#039;IMPULSO&#039;) OR upper(subproducto) LIKE &#039;%IMPULSO%&#039;  then 1 else 0 end) flg_impulso,
               MAX(CASE WHEN upper(producto) in (&#039;FINANCIAMIENTO DE VENTAS&#039;) or upper(producto) like &#039;%DESCUENTO%&#039; 
                   or upper(producto) like &#039;%NEGOCIABLE%&#039; or upper(producto) like &#039;%FINANCIAMIENTO%&#039; then 1 else 0 end) flg_financiamiento,
               MAX(CASE WHEN upper(producto) in (&#039;ECOSISTEMAS&#039;) OR upper(subproducto) LIKE &#039;%ECOSISTEMAS%&#039; then 1 else 0 end) flg_ecosistemas,
               CAST(MAX(
                   CASE WHEN oferta like &#039;%Venta%&#039; OR oferta LIKE &#039;&#039; then Null 
                        WHEN LENGTH(split_part(oferta, &#039;Oferta 2: &#039;, 2)) > 0 then split_part(oferta, &#039;Oferta 2: &#039;, 2) 
                        WHEN LENGTH(split_part(oferta, &#039;Oferta 2:&#039;, 2)) > 0 then split_part(oferta, &#039;Oferta 2:&#039;, 2) 
                        WHEN LENGTH(split_part(oferta, &#039;/&#039;, 2)) > 0 then split_part(oferta, &#039;/&#039;, 2) 
                   ELSE oferta end) AS DOUBLE) oferta,
               MAX(plazo) plazo,
               substring(MAX(bucket),1,1) bucket,
               MAX(CASE WHEN UPPER(canal_tlv) LIKE &#039;%B%&#039; THEN 1 ELSE 0 END) flg_canal_b,
               MAX(CASE WHEN UPPER(canal_tlv) LIKE &#039;%C%&#039; THEN 1 ELSE 0 END) flg_canal_c,
               MAX(tasa) tasa,
               MAX(CASE WHEN tasa_piso is null and tasa is not null then tasa -0.02 else tasa_piso end) tasa_piso,
               MAX(CASE WHEN upper(tipo_carga) LIKE &#039;%REGULAR%&#039; THEN 1 ELSE 0 END) FLG_CARGA_REGULAR,
               
                MAX(CASE WHEN upper(tipo_carga) LIKE &#039;%PRUEBA%&#039; THEN 1
                        WHEN upper(tipo_carga) LIKE &#039;%PILOTO%&#039; THEN 1 ELSE 0 END ) FLG_TIPO_CARGA_PILOTO,
                MAX(CASE WHEN upper(tipo_carga) LIKE &#039;%REGULAR%&#039; THEN 1
                         WHEN upper(tipo_carga) LIKE &#039;%ASINCRONA%&#039; THEN 1 ELSE 0 END) FLG_TIPO_CARGA_REGULAR,
                MAX(CASE WHEN upper(tipo_carga) LIKE &#039;%RECARGA%&#039; THEN 1
                         WHEN upper(tipo_carga) LIKE &#039;%COMPRA DE DATOS%&#039; THEN 1 ELSE 0 END) FLG_TIPO_CARGA_RECARGA,
                MAX(CASE WHEN upper(tipo_carga) LIKE &#039;%SIN CELULAR%&#039; THEN 1 ELSE 0 END) FLG_TIPO_CARGA_SIN_CELULAR,  
                MAX(CASE WHEN upper(tipo_carga) LIKE &#039;%RESCATE%&#039; THEN 1 ELSE 0 END) FLG_TIPO_CARGA_RESCATE,  
                MAX(CASE WHEN tipo_carga LIKE &#039;%TUBERIA%&#039; THEN 1 ELSE 0 END) FLG_TIPO_CARGA_TUBERIA,
                MAX(CASE WHEN tipo_carga LIKE (&#039;&#039;) THEN 1 ELSE 0 END) FLG_TIPO_CARGA_VACIO,

               periodo_campania p_periodo
        FROM disc_comercial.HM_DESPLIEGUE_CAMPANIAS 
        WHERE 1 = 1
        AND (
              producto in (&#039;NUEVOS PRE&#039;, &#039;ECOSISTEMAS&#039;, &#039;NUEVOS APPAS&#039;, &#039;NUEVOS APCONSUMO&#039;, &#039;NUEVOS AP&#039;, &#039;NUEVOS AP SUPER_AGIL&#039;,
                           &#039;IMPULSO&#039;, &#039;PAGARE CERRADO&#039;)

              OR (
                   (  
                      upper(producto) like &#039;%DESCUENTO%&#039; OR upper(producto) like &#039;%NEGOCIABLE%&#039; OR upper(producto) like &#039;%FINANCIAMIENTO%&#039;
                   ) 
                   AND 
                   (
                     upper(SUBPRODUCTO) like &#039;%PROACTIVO%&#039; or upper(SUBPRODUCTO) like &#039;%DESEMBOLSADO%&#039;
                   )
              )

        )
        AND UPPER(TIPO_CARGA) = &#039;REGULAR&#039;
        AND periodo_campania is not null
        AND periodo_campania not like &#039;&#039;
        GROUP BY periodo_campania, num_ruc
"""
)