-- @desvío D1: pool TED con las fechas del CSV 2020-2023 corregidas (día/mes intercambiados por el scraper) CREATE OR REPLACE TABLE tpc AS WITH csv AS (SELECT DISTINCT aviso, d FROM ted_fix WHERE y BETWEEN 2020 AND 2023) SELECT tp.* REPLACE ( CASE WHEN c.aviso IS NOT NULL AND day(tp.tpub) <= 12 AND day(tp.tpub) <> month(tp.tpub) THEN make_date(year(tp.tpub), day(tp.tpub), month(tp.tpub)) ELSE tp.tpub END AS tpub, CASE WHEN c.aviso IS NOT NULL AND day(tp.tadj) <= 12 AND day(tp.tadj) <> month(tp.tadj) THEN make_date(year(tp.tadj), day(tp.tadj), month(tp.tadj)) ELSE tp.tadj END AS tadj), (c.aviso IS NOT NULL) AS csv_2020_2023 FROM tp LEFT JOIN csv c ON c.aviso = tp.aviso AND c.d = tp.tpub ; --; -- @filas corregidas SELECT csv_2020_2023, count(*), count(DISTINCT aviso) FROM tpc GROUP BY 1 ; --; -- @coherencia de la fecha de adjudicación (envío no ambiguo, adjudicación ambigua): tal cual frente a intercambiada SELECT year(d) y, count(*) n, sum((a <= d + 5 AND d - a <= 400)::INT) tal_cual_ok, sum((make_date(year(a), day(a), month(a)) <= d + 5 AND d - make_date(year(a), day(a), month(a)) <= 400)::INT) intercambiada_ok FROM ted_fix WHERE y BETWEEN 2018 AND 2023 AND NOT amb AND a IS NOT NULL AND day(a) <= 12 AND day(a) <> month(a) GROUP BY 1 ORDER BY 1 ; --; -- @comprobación: las 22 fechas de la muestra frente a la API de TED (publicación = envío + unos días) SELECT aviso, min(tpub) FROM tpc WHERE aviso IN ('221391-2023','278642-2023','292714-2023','30529-2023','328623-2023','352373-2023','367436-2023','367472-2023','371045-2023','409440-2023','426250-2023','427292-2023','450121-2023','499726-2023','539912-2023','544006-2023','695225-2023','755154-2023','757583-2023','762211-2023','764744-2023','792600-2023') GROUP BY 1 ORDER BY 1