-- 01. Base persistente (tmp/em1b.duckdb): una fila por licitación (§2 del pre-registro). CREATE OR REPLACE TABLE filas AS SELECT u.licitacion_id, CASE WHEN u.tabla_origen = 'resultados_licitaciones' OR (u.tabla_origen = 'licitaciones_sin_resultado' AND u.url_origen LIKE 'https://contrataciondelestado.es%') THEN 'propio' ELSE 'agregacion' END AS feed, u.valor_estimado_eur_expediente, u.imp_lic_eur_expediente, u.tipo_contrato, u.procedimiento_native, u.cpv_division, u.ccaa, u.sara, u.fecha_pub, u.fecha_fin_ofertas, u.fecha_adj, o.organo_clave, o.entidad_clave, trim(regexp_replace(regexp_replace(lower(strip_accents(coalesce(u.objeto, ''))), '[^a-z ]', ' ', 'g'), '\s+', ' ', 'g')) AS objeto_norm FROM u LEFT JOIN ofi o ON o.organo_nif IS NOT DISTINCT FROM u.organo_nif AND o.organo_nombre IS NOT DISTINCT FROM u.organo_nombre AND o.dir3 IS NOT DISTINCT FROM u.dir3 WHERE u.source = 'PLACSP' AND u.vigente AND u.es_contrato AND NOT coalesce(u.es_menor, FALSE) AND u.tabla_origen IN ('resultados_licitaciones', 'resultados_agregacion', 'licitaciones_sin_resultado') AND u.unidad IN ('adjudicacion', 'desierto', 'licitacion:sin_resultado', 'licitacion:adjudicada_sin_detalle') AND NOT coalesce(u.anulado_en_origen, FALSE) AND u.copia_de IS NULL AND coalesce(u.rol_am, '') NOT IN ('basado', 'basado_sda') AND coalesce(u.procedimiento_native, '') NOT IN ('Derivado de acuerdo marco', 'Basado en sistema dinámico de adquisición') AND coalesce(u.tramitacion, '') <> 'emergencia' AND least(u.fecha_pub, u.fecha_fin_ofertas, u.fecha_adj) <= DATE '2026-12-31' AND greatest(u.fecha_pub, u.fecha_fin_ofertas, u.fecha_adj) >= DATE '2021-06-01'; CREATE OR REPLACE TABLE tipo_org AS SELECT organo_clave, any_value(tipo) tipo FROM org_tipo GROUP BY 1; CREATE OR REPLACE TABLE ent AS SELECT entidad_clave, any_value(administracion) administracion FROM read_parquet('/d/unified_20261018.identidad/entidades.parquet') GROUP BY 1; CREATE OR REPLACE TABLE lic AS WITH l AS ( SELECT feed, licitacion_id, max(valor_estimado_eur_expediente) ve, max(imp_lic_eur_expediente) pbl, min(tipo_contrato) tipo_contrato, min(procedimiento_native) proc_nat, min(cpv_division) cpv_division, min(ccaa) ccaa, bool_or(sara) sara, least(min(fecha_pub), min(fecha_fin_ofertas), min(fecha_adj)) fecha_ref, min(fecha_pub) fecha_pub, min(organo_clave) organo_clave, min(entidad_clave) entidad_clave, min(objeto_norm) objeto_norm, count(DISTINCT valor_estimado_eur_expediente) n_ve_distintos, count(*) filas FROM filas GROUP BY 1, 2) SELECT l.*, coalesce(t.tipo, 'sin_tipo') tipo_org, e.administracion, (coalesce(t.tipo, '') = 'age') AS age, (coalesce(t.tipo, '') = 'age' OR (coalesce(t.tipo, '') IN ('investigacion', 'salud') AND e.administracion = 'Administración General del Estado')) AS age_amp, CASE WHEN l.tipo_contrato IN ('Servicios', 'Suministros') THEN 'ss' WHEN l.tipo_contrato = 'Obras' THEN 'obras' WHEN l.tipo_contrato IN ('Concesion_obras', 'Concesion_servicios') THEN 'conc' ELSE 'otra' END AS clase, year(l.fecha_ref) || 'T' || quarter(l.fecha_ref) AS trimestre, year(l.fecha_ref) AS anio, l.proc_nat IN ('Abierto', 'Abierto simplificado', 'Restringido', 'Negociado con publicidad', 'Licitación con negociación', 'Diálogo competitivo', 'Asociación para la innovación') AS proc_exante, length(l.objeto_norm) >= 10 AS objeto_valido FROM l LEFT JOIN tipo_org t USING (organo_clave) LEFT JOIN ent e USING (entidad_clave) WHERE l.fecha_ref BETWEEN DATE '2022-01-01' AND DATE '2026-09-30'; -- n_cpv y n_obj sobre la población (ambos feeds, cualquier clase, VE e importe), por año de fecha_ref CREATE OR REPLACE TABLE lic2 AS SELECT l.*, c.n_cpv, CASE WHEN l.objeto_valido THEN ob.n_obj END n_obj FROM lic l LEFT JOIN (SELECT organo_clave, cpv_division, anio, count(*) n_cpv FROM lic WHERE organo_clave IS NOT NULL AND cpv_division IS NOT NULL GROUP BY ALL) c USING (organo_clave, cpv_division, anio) LEFT JOIN (SELECT organo_clave, objeto_norm, anio, count(*) n_obj FROM lic WHERE organo_clave IS NOT NULL AND objeto_valido GROUP BY ALL) ob USING (organo_clave, objeto_norm, anio); DROP TABLE lic; ALTER TABLE lic2 RENAME TO lic; DROP TABLE filas; -- @ lic: licitaciones por feed y año, con VE, órgano, CPV, clase SELECT feed, anio, count(*) lic, count(ve) FILTER (WHERE ve > 0) con_ve, count(pbl) con_pbl, count(organo_clave) con_org, count(cpv_division) con_cpv, count(*) FILTER (WHERE clase='ss') ss, count(*) FILTER (WHERE clase='obras') obras, count(*) FILTER (WHERE clase='conc') conc, count(*) FILTER (WHERE age) age, count(*) FILTER (WHERE age_amp) age_amp, count(*) FILTER (WHERE n_ve_distintos > 1) ve_varios FROM lic GROUP BY ALL ORDER BY 1, 2; -- @ duplicados de licitacion_id entre feeds (debería ser 0) SELECT count(*) - count(DISTINCT licitacion_id) dup FROM lic; -- @ fecha_ref frente a fecha_pub (días), por feed SELECT feed, count(*) n, count(*) FILTER (WHERE fecha_ref < fecha_pub) ref_antes_pub, quantile_cont(fecha_pub - fecha_ref, 0.5) FILTER (WHERE fecha_ref < fecha_pub) med_dias, count(*) FILTER (WHERE year(fecha_ref) <> year(fecha_pub)) cambia_anio FROM lic GROUP BY 1;