-- E3 · Concordancia del nº de ofertas: TED eForms (BT-760 «tenders» por resultado de lote) frente a CODICE -- (PLACSP, ReceivedTenderQuantity → num_ofertas) donde ambos existen. Solo lectura. -- Cruce exacto: NIF del órgano + expediente normalizado (único en la PLACSP) + lote (internal_id_lot = lote de -- la PLACSP; o licitación sin lotes y aviso de un solo resultado). Ventana: aviso TED publicado entre 30 días antes y -- 730 días después de la adjudicación de la PLACSP. CREATE MACRO nexp(x) AS upper(regexp_replace(coalesce(x, ''), '[^A-Za-z0-9]', '', 'g'));; CREATE MACRO nnif0(x) AS upper(regexp_replace(coalesce(x, ''), '[^A-Za-z0-9]', '', 'g'));; CREATE MACRO nnif(x) AS CASE WHEN regexp_matches(nnif0(x), '^ES[A-Z0-9]{9}$') THEN substr(nnif0(x), 3) ELSE nnif0(x) END;; CREATE TEMP TABLE ted AS SELECT ted_notice_id, lot_result_id, any_value(notice_type) notice_type, any_value(year) anio, any_value(TRY_CAST(publication_date AS DATE)) fpub, any_value(nnif(cae_nationalid)) nif_org, any_value(nexp(coalesce(nullif(expediente_sin_lote, ''), internal_id_proc))) exp, any_value(internal_id_lot) lote_int, any_value(TRY_CAST(number_offers AS INTEGER)) nof_ted, any_value(buyer_profile) perfil, any_value(winner_selection_status) estado, any_value(n_filas_aviso) n_filas FROM '/ted/ted_es_can.parquet' WHERE source = 'api_v3' AND _en_ultima_descarga AND notice_type IN ('can-standard', 'can-social', 'can-desg', 'can-tran') AND coalesce(_xml_eforms, '') = '' AND coalesce(lot_result_id, '') <> '' AND coalesce(cancelled, '0') <> '1' GROUP BY 1, 2;; CREATE TEMP TABLE ted_n AS SELECT ted_notice_id, count(*) n_res FROM ted GROUP BY 1;; CREATE TEMP TABLE lic AS SELECT id, nnif(nif_organo) nif_org, nexp(expediente) exp, TRY_CAST(fecha_adjudicacion AS DATE) fadj, n_lotes FROM '/data/licitaciones-historico/nacional/salida/licitaciones_completo.parquet' WHERE es_ultima_version AND conjunto = 'licitaciones' AND length(nexp(expediente)) >= 5 AND regexp_matches(expediente, '[0-9]');; CREATE TEMP TABLE clave_unica AS SELECT nif_org, exp, any_value(id) id FROM lic GROUP BY 1, 2 HAVING count(DISTINCT id) = 1;; CREATE TEMP TABLE res AS SELECT id, coalesce(lote, '') lote, count(DISTINCT num_ofertas) n_valores, max(num_ofertas)::INTEGER nof_placsp, max(num_ofertas_pyme)::INTEGER nof_pyme_placsp, max(TRY_CAST(fecha_adjudicacion AS DATE)) fadj FROM '/data/licitaciones-historico/nacional/salida/licitaciones_completo_resultados.parquet' WHERE es_ultima_version AND conjunto = 'licitaciones' GROUP BY 1, 2;; CREATE TEMP TABLE res_n AS SELECT id, count(*) n_lotes_res, sum((lote = '')::int) n_sin_lote FROM res GROUP BY 1;; CREATE TEMP TABLE pares AS SELECT t.*, c.id, r.lote, r.nof_placsp, r.nof_pyme_placsp, r.n_valores, r.fadj, CASE WHEN r.lote = t.lote_int THEN 'lote' ELSE 'sin_lotes' END via FROM ted t JOIN ted_n tn USING (ted_notice_id) JOIN clave_unica c ON c.nif_org = t.nif_org AND c.exp = t.exp AND length(t.exp) >= 5 JOIN res_n rn ON rn.id = c.id JOIN res r ON r.id = c.id AND (r.lote = t.lote_int OR (r.lote = '' AND rn.n_lotes_res = 1 AND tn.n_res = 1)) WHERE t.fpub BETWEEN r.fadj - 30 AND r.fadj + 730;;