-- @corrección del intercambio día/mes del CSV de TED (desvío declarado): fecha esperada por el número de publicación CREATE OR REPLACE TABLE ted_fix AS WITH b AS (SELECT y, nid, num, d, a, day(d) <= 12 AND month(d) <= 12 AS amb, date_diff('day', DATE '2000-01-01', d) AS e FROM ted_disp), w AS (SELECT *, median(CASE WHEN NOT amb THEN e END) OVER (PARTITION BY y ORDER BY num RANGE BETWEEN 40000 PRECEDING AND 40000 FOLLOWING) AS med FROM b), s AS (SELECT *, CASE WHEN amb THEN make_date(year(d), day(d), month(d)) END AS dsw FROM w) SELECT y, nid, num, substr(nid,5)||'-'||y AS aviso, d, a, amb, med, (amb AND abs(date_diff('day', DATE '2000-01-01', dsw) - med) + 15 < abs(e - med)) AS swapped, CASE WHEN amb AND abs(date_diff('day', DATE '2000-01-01', dsw) - med) + 15 < abs(e - med) THEN dsw ELSE d END AS d_fix FROM s ; --; -- @por año: filas ambiguas, intercambiadas, sin referencia SELECT y, count(*) n, sum(amb::INT) amb, sum(swapped::INT) swapped, sum((amb AND med IS NULL)::INT) sin_ref, round(100.0*sum(swapped::INT)/nullif(sum(amb::INT),0),1) pct_amb_intercambiadas FROM ted_fix GROUP BY 1 ORDER BY 1 ; --; -- @fecha de adjudicación en las intercambiadas: ¿también intercambiada? (a <= envío corregido y a menos de 400 días) SELECT y, count(*) n, sum((a <= d_fix + 5 AND d_fix - a <= 400)::INT) tal_cual_ok, sum((make_date(year(a), day(a), month(a)) <= d_fix + 5 AND d_fix - make_date(year(a), day(a), month(a)) <= 400)::INT) intercambiada_ok FROM ted_fix WHERE swapped AND a IS NOT NULL AND day(a) <= 12 GROUP BY 1 ORDER BY 1 ; --; -- @comprobación con la muestra (fechas de la API de TED) SELECT aviso, d, d_fix, swapped FROM ted_fix WHERE aviso IN ('221391-2023','292714-2023','30529-2023','352373-2023','409440-2023','426250-2023','427292-2023','499726-2023','539912-2023','544006-2023','328623-2023','278642-2023','367436-2023','764744-2023') ORDER BY 1