-- @unidades (licitación-lote) con su estado de casamiento a +365 y +730 CREATE OR REPLACE TABLE unc AS WITH g AS ( SELECT licitacion_id||'#'||lote AS unit, any_value(licitacion_id) lic, bool_or(in_u1) u1, bool_or(in_u2) u2, bool_or(in_u2x) u2x, any_value(estrato) estrato, year(min(ref)) anio, min(ref) AS ref, min(fecha_formalizacion) AS fform, any_value(tipo_contrato) tipo, any_value(procedimiento_native) proc, any_value(organo_clave) organo_clave, coalesce(any_value(organo_tipo),'sin_tipo') organo_tipo, any_value(organo_nombre) organo_nombre, max(rol_am) rol_am FROM uf GROUP BY 1) SELECT g.*, m.est est365, m.aviso aviso365, m2.est est730, m2.aviso aviso730 FROM g LEFT JOIN M_365c m USING (unit) LEFT JOIN M_730c m2 USING (unit) ; --; CREATE OR REPLACE MACRO wil_lo(x, n) AS CASE WHEN n=0 THEN NULL ELSE ((x/n + 1.96^2/(2*n)) - 1.96*sqrt((x/n)*(1-x/n)/n + 1.96^2/(4*n^2)))/(1+1.96^2/n) END ; --; CREATE OR REPLACE MACRO wil_hi(x, n) AS CASE WHEN n=0 THEN NULL ELSE ((x/n + 1.96^2/(2*n)) + 1.96*sqrt((x/n)*(1-x/n)/n + 1.96^2/(4*n^2)))/(1+1.96^2/n) END ; --; -- @U1 con publicidad: tasa por año (todas las estrategias; cota baja = solo E1, la que se espera aceptada) SELECT anio, count(*) n, sum((est365 IS NOT NULL)::INT) x, round(100.0*sum((est365 IS NOT NULL)::INT)/count(*),1) pct, round(100*wil_lo(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) lo, round(100*wil_hi(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) hi, sum((est365='E1')::INT) e1, sum((est365='E2')::INT) e2, sum((est365='E3')::INT) e3, sum((est365='E4')::INT) e4, sum((est365='E5')::INT) e5, round(100.0*sum((est730 IS NOT NULL)::INT)/count(*),1) pct730 FROM unc WHERE u1 AND estrato='con_publicidad' GROUP BY 1 ORDER BY 1 ; --; -- @U1 negociado sin publicidad: tasa por año SELECT anio, count(*) n, sum((est365 IS NOT NULL)::INT) x, round(100.0*sum((est365 IS NOT NULL)::INT)/count(*),1) pct, round(100*wil_lo(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) lo, round(100*wil_hi(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) hi FROM unc WHERE u1 AND estrato='nsp' GROUP BY 1 ORDER BY 1 ; --; -- @U2 (serie por umbral) con publicidad: tasa por año SELECT anio, count(*) n, sum((est365 IS NOT NULL)::INT) x, round(100.0*sum((est365 IS NOT NULL)::INT)/count(*),1) pct, round(100*wil_lo(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) lo, round(100*wil_hi(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) hi, round(100.0*sum((est365='E1')::INT)/count(*),1) pct_e1 FROM unc WHERE u2 AND estrato='con_publicidad' GROUP BY 1 ORDER BY 1 ; --; -- @U2x (sobre umbral pero declarado sara=FALSE): tasa por año (descriptivo) SELECT anio, count(*) n, round(100.0*sum((est365 IS NOT NULL)::INT)/count(*),1) pct FROM unc WHERE u2x AND NOT u2 AND estrato='con_publicidad' GROUP BY 1 ORDER BY 1 ; --; -- @U1 con publicidad, por tipo de contrato SELECT tipo, count(*) n, round(100.0*sum((est365 IS NOT NULL)::INT)/count(*),1) pct, round(100*wil_lo(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) lo, round(100*wil_hi(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) hi FROM unc WHERE u1 AND estrato='con_publicidad' GROUP BY 1 ORDER BY 2 DESC ; --; -- @U1 con publicidad, por procedimiento y rol en acuerdo marco SELECT proc, coalesce(rol_am,'-') rol_am, count(*) n, round(100.0*sum((est365 IS NOT NULL)::INT)/count(*),1) pct, round(100*wil_lo(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) lo, round(100*wil_hi(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) hi FROM unc WHERE u1 AND estrato='con_publicidad' GROUP BY 1,2 ORDER BY 3 DESC ; --; -- @U1 con publicidad, por tipo de órgano SELECT organo_tipo, count(*) n, count(DISTINCT organo_clave) organos, round(100.0*sum((est365 IS NOT NULL)::INT)/count(*),1) pct, round(100*wil_lo(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) lo, round(100*wil_hi(sum((est365 IS NOT NULL)::INT)::DOUBLE, count(*)),1) hi FROM unc WHERE u1 AND estrato='con_publicidad' GROUP BY 1 ORDER BY 2 DESC ; --; -- @U1 con publicidad, por licitación (al menos unc lote casado) SELECT anio, count(*) n_lic, round(100.0*sum(c::INT)/count(*),1) pct FROM (SELECT lic, min(anio) anio, bool_or(est365 IS NOT NULL) c FROM unc WHERE u1 AND estrato='con_publicidad' GROUP BY lic) GROUP BY 1 ORDER BY 1 ; --; -- @censura: casadas en 2023-2024 (U1) con TED más de 289 días después de ref SELECT m.est, count(*) n, sum((t.tpub - unc.ref > 289)::INT) tarde, round(100.0*sum((t.tpub - unc.ref > 289)::INT)/count(*),2) pct FROM unc JOIN M_365c m USING (unit) JOIN (SELECT aviso, min(tpub) tpub FROM tpc GROUP BY 1) t ON t.aviso=m.aviso WHERE unc.u1 AND unc.estrato='con_publicidad' AND unc.anio IN (2023,2024) GROUP BY ROLLUP(1) ORDER BY 1 ; --; -- @plazo: días de formalización (o ref) a la publicación TED, U1 con publicidad casadas SELECT unc.anio, count(*) n, median(t.tpub - coalesce(unc.fform, unc.ref)) mediana_dias, round(100.0*sum((t.tpub - coalesce(unc.fform, unc.ref) <= 30)::INT)/count(*),1) pct_30d FROM unc JOIN M_365c m USING (unit) JOIN (SELECT aviso, min(tpub) tpub FROM tpc GROUP BY 1) t ON t.aviso=m.aviso WHERE unc.u1 AND unc.estrato='con_publicidad' GROUP BY 1 ORDER BY 1