DROP TABLE IF EXISTS d_mdl_vpc_disc.STAGE_HM_SUNAT_RENIEC_AUTOCOMPLETADO

CREATE TABLE d_mdl_vpc_disc.STAGE_HM_SUNAT_RENIEC_AUTOCOMPLETADO
    WITH ( format = 'Parquet', 
             parquet_compression = 'SNAPPY', 
             partitioned_by = ARRAY['p_periodo'],
             external_location= 's3://sagemaker-us-east-1-058528764918/vpc/aceptacion/athena_2/STAGE_HM_SUNAT_RENIEC_AUTOCOMPLETADO/'
           )
    AS (
    
SELECT case when (AA.ciiu_val like '' or AA.ciiu_val is null) AND BB.ciiu_val IS NOT NULL THEN BB.ciiu_val ELSE AA.ciiu_val END ciiu_val_autocomplete,
    case when BB.ciiu_val IS NOT NULL THEN BB.ciiu_val ELSE AA.ciiu_val END ciiu_val_update,
    AA.*,
    AA.periodo_val p_periodo
FROM(
    SELECT ROW_NUMBER() OVER(PARTITION by periodo_val, numruc_val ORDER BY tiempo_deuda_tributaria_amt DESC) ORDEN,
      * 
      FROM e_perm_aws.t_fact_vpc_agg_sunat_reniec 
      WHERE numruc_val IN (select num_ruc from d_mdl_vpc_disc.STAGE_HM_BASE_CAMPANIA)
      AND periodo_val >= '202110'
) AA
LEFT JOIN (
     SELECT periodo_val, numruc_val, ciiu_val,
            ROW_NUMBER() OVER(PARTITION by numruc_val ORDER BY periodo_val DESC) orden_periodo
     FROM e_perm_aws.t_fact_vpc_agg_sunat_reniec 
     where periodo_val in ('202209', '202211')
     AND numruc_val IN (select num_ruc from d_mdl_vpc_disc.STAGE_HM_BASE_CAMPANIA)
     AND ciiu_val not like ''
     AND ciiu_val IS NOT NULL
     AND ciiu_val not in ('SV', '.')
) BB
ON AA.numruc_val = BB.numruc_val and BB.orden_periodo = 1
WHERE AA.ORDEN = 1

)

SELECT periodo_val,
      fecha_informacion_dt,
       COUNT(1) conteo_ruc,
       count(distinct(numruc_val)) rucs_unicos,
       SUM(CASE WHEN ciiu_val like '' then 1 else 0 end) flg_string_vacio,
       SUM(CASE WHEN ciiu_val_autocomplete like '' then 1 else 0 end) flg_string_vacio_2,
       SUM(CASE WHEN ciiu_val_update like '' then 1 else 0 end) flg_string_vacio_3
FROM d_mdl_vpc_disc.STAGE_HM_SUNAT_RENIEC_AUTOCOMPLETADO
GROUP BY periodo_val, fecha_informacion_dt
ORDER BY periodo_val DESC, fecha_informacion_dt