Query editor
with t_volume as (SELECT-- date(block_time) as dt,regexp_extract(event_market_name, 'Highest temperature in (.*?) on', 1) AS city,regexp_extract(question, '°([CF])', 1) AS unit,sum(amount) as volumeFROM polymarket_polygon.market_tradeswHERE block_time >= TIMESTAMP '2026-06-01' and block_time < TIMESTAMP '2026-06-27'AND event_market_name LIKE 'Highest temperature%'-- and regexp_extract(event_market_name, 'Highest temperature in (.*?) on', 1) = 'London'group by 1,2),t_city as (select regexp_extract(event_market_name, 'Highest temperature in (.*?) on', 1) AS city,regexp_extract(market_description,'recorded at the (.*?) Station',1) as station,regexp_replace(regexp_extract(market_description,'available here:[\r\n\t ]*(https?://[^\r\n\t ]+)',1),'[.]$','') AS source,regexp_extract(regexp_replace(regexp_extract(market_description,'available here:[\r\n\t ]*(https?://[^\r\n\t ]+)',1),'[.]$',''),'/([^/]+)$',1) AS airport_codefrom polymarket_polygon.market_detailswhere event_market_name LIKE 'Highest temperature%'and resolved_on_timestamp >= TIMESTAMP '2026-06-01' and resolved_on_timestamp < TIMESTAMP '2026-06-27'group by 1,2,3,4)select a.*, station, source, airport_codefrom t_volume a left join t_city b on a.city = b.city