CREATE VIEW rl0 AS SELECT * FROM '/e3/run/ted/ted_resultados_lote_es.parquet';; CREATE VIEW li0 AS SELECT * FROM '/e3/run/ted/ted_licitadores_es.parquet';; CREATE TEMP TABLE perfil AS SELECT ted_notice_id, any_value(buyer_profile) buyer_profile, any_value(cae_nationalid) cae_nif FROM read_parquet(['/ted/ted_can_2023_ES_eforms.parquet','/ted/ted_can_2024_ES_api.parquet','/ted/ted_can_2025_ES_api.parquet','/ted/ted_can_2026_ES_api_en_curso.parquet'], union_by_name=true) GROUP BY 1;; CREATE MACRO plat(p) AS CASE WHEN p LIKE '%contrataciondelestado%' THEN 'PLACSP' WHEN p LIKE '%euskadi%' THEN 'Euskadi' WHEN p LIKE '%contractaciopublica%' THEN 'Cataluña' WHEN p LIKE '%juntadeandalucia%' OR p LIKE '%junta-andalucia%' THEN 'Andalucía' WHEN p LIKE '%madrid.org%' OR p LIKE '%comunidad.madrid%' THEN 'Madrid (CAM)' WHEN p LIKE '%contratosdegalicia%' THEN 'Galicia' WHEN p IS NULL OR p='' THEN 'sin perfil' ELSE 'otros' END;; CREATE MACRO clase_id(x) AS CASE WHEN x='' OR x IS NULL THEN 'sin_id' WHEN regexp_matches(upper(x), '^(ES)?[ABCDEFGHJNPQRSUVW][0-9]{7}[0-9A-J]$') THEN 'nif_juridica' WHEN regexp_matches(upper(x), '^(ES)?([0-9]{8}[A-Z]|[XYZ][0-9]{7}[A-Z])$') THEN 'nif_fisica' WHEN regexp_matches(x, '^[0-9]+(\.0)?$') THEN 'numerico' WHEN x LIKE '%*%' THEN 'enmascarado' WHEN x LIKE '%---%' THEN 'grupo' ELSE 'otro' END;; CREATE TEMP TABLE rl AS SELECT r.*, plat(p.buyer_profile) plataforma FROM rl0 r LEFT JOIN perfil p USING (ted_notice_id) WHERE buyer_country='ESP' AND notice_type IN ('can-standard','can-social','can-desg','can-tran');; CREATE TEMP TABLE li AS SELECT l.*, plat(p.buyer_profile) plataforma, clase_id(tenderer_nationalid) clase FROM li0 l LEFT JOIN perfil p USING (ted_notice_id) WHERE buyer_country='ESP' AND notice_type IN ('can-standard','can-social','can-desg','can-tran');; CREATE TEMP TABLE rdist AS SELECT ted_notice_id, lot_result_id, count(*) ofertas, count(DISTINCT coalesce(nullif(tenderer_nationalid,''), tenderer_name)) licitadores_distintos, sum((es_ganadora='false')::int) no_ganadoras FROM li GROUP BY 1,2;; CREATE TEMP TABLE rl2 AS SELECT r.*, coalesce(d.licitadores_distintos,0) lic_dist, coalesce(d.no_ganadoras,0) no_gan, TRY_CAST(st_tenders AS INT) tenders FROM rl r LEFT JOIN rdist d USING (ted_notice_id, lot_result_id);;