-- @muestra de ids y fechas SELECT source, year, ted_notice_id, dt_dispatch, publication_date, dt_award FROM read_parquet('/h/ted/ted_es_can.parquet') WHERE source='csv_bulk' AND year=2023 LIMIT 3 ; --; -- @día/mes intercambiados: fecha de envío frente al número de publicación (CSV), por año CREATE OR REPLACE TABLE ted_disp AS SELECT DISTINCT year AS y, ted_notice_id AS nid, CAST(dt_dispatch AS DATE) AS d, CAST(dt_award AS DATE) AS a, TRY_CAST(substr(ted_notice_id, 5) AS INTEGER) AS num FROM read_parquet('/h/ted/ted_es_can.parquet') WHERE source='csv_bulk' AND dt_dispatch IS NOT NULL ; --; WITH b AS (SELECT *, num // 1000 AS bin, day(d) <= 12 AS amb FROM ted_disp), m AS (SELECT y, bin, median(dayofyear(d)) md FROM b WHERE NOT amb GROUP BY 1,2), s AS (SELECT b.*, m.md, CASE WHEN amb THEN make_date(year(d), day(d), month(d)) END AS dsw FROM b JOIN m USING (y, bin)) SELECT y, count(*) n, sum(amb::INT) ambiguas, sum(CASE WHEN amb AND abs(dayofyear(dsw)-md) + 20 < abs(dayofyear(d)-md) THEN 1 ELSE 0 END) mejor_intercambiada, sum(CASE WHEN amb AND abs(dayofyear(d)-md) + 20 < abs(dayofyear(dsw)-md) THEN 1 ELSE 0 END) mejor_tal_cual, sum(CASE WHEN NOT amb AND abs(dayofyear(d)-md) > 30 THEN 1 ELSE 0 END) no_amb_lejos FROM s GROUP BY 1 ORDER BY 1