Query editor
with lp_address as (SELECTlp_address,namespaceFROMquery_2031078wheretoken0 = 0x6982508145454ce325ddbe47a25d4ec3d2311933or token1 = 0x6982508145454ce325ddbe47a25d4ec3d2311933),tag_summary as (select lp_address as address, 'dex' as tag, 'dex' as name from lp_address),-- 2015-10-30 ~ 2024-10-27time_seq as (SELECT time FROM UNNEST (SEQUENCE(TIMESTAMP '2015-10-30 11:00', date_add('day',365*1,timestamp '2015-10-30 11:00'),INTERVAL '1' HOUR)) TBL (time)union allSELECT time FROM UNNEST (SEQUENCE(date_add('day',365*1,timestamp '2015-10-30 11:00'), date_add('day',365*2,timestamp '2015-10-30 11:00'),INTERVAL '1' HOUR)) TBL (time)union allSELECT time FROM UNNEST (SEQUENCE(date_add('day',365*2,timestamp '2015-10-30 11:00'), date_add('day',365*3,timestamp '2015-10-30 11:00'),INTERVAL '1' HOUR)) TBL (time)union allSELECT time FROM UNNEST (SEQUENCE(date_add('day',365*3,timestamp '2015-10-30 11:00'), date_add('day',365*4,timestamp '2015-10-30 11:00'),INTERVAL '1' HOUR)) TBL (time)union allSELECT time FROM UNNEST (SEQUENCE(date_add('day',365*4,timestamp '2015-10-30 11:00'), date_add('day',365*5,timestamp '2015-10-30 11:00'),INTERVAL '1' HOUR)) TBL (time)union allSELECT time FROM UNNEST (SEQUENCE(date_add('day',365*5,timestamp '2015-10-30 11:00'), date_add('day',365*6,timestamp '2015-10-30 11:00'),INTERVAL '1' HOUR)) TBL (time)union allSELECT time FROM UNNEST (SEQUENCE(date_add('day',365*6,timestamp '2015-10-30 11:00'), date_add('day',365*7,timestamp '2015-10-30 11:00'),INTERVAL '1' HOUR)) TBL (time)union allSELECT time FROM UNNEST (SEQUENCE(date_add('day',365*7,timestamp '2015-10-30 11:00'), date_add('day',365*8,timestamp '2015-10-30 11:00'),INTERVAL '1' HOUR)) TBL (time)union allSELECT time FROM UNNEST (SEQUENCE(date_add('day',365*8,timestamp '2015-10-30 11:00'), date_add('day',365*9,timestamp '2015-10-30 11:00'),INTERVAL '1' HOUR)) TBL (time)),hourly_token_price_detail_without_seq as (withprice_from_rpc as (select date_trunc('hour',minute)as hourly_time, AVG(price) as median_price from prices.usd where contract_address=0x6982508145454ce325ddbe47a25d4ec3d2311933 group by date_trunc('hour',minute) limit 10),price_from_dex as (select t1.hour as hourly_time,t1.median_price from dex.prices t1 left join price_from_rpc t2 on t1.hour=t2.hourly_time where t1.contract_address=0x6982508145454ce325ddbe47a25d4ec3d2311933 and t2.hourly_time is null)select * from price_from_rpcUNIONselect * from price_from_dex-- union-- select * from query_2427803 where hourly_time > date_trunc('hour', timestamp '2023-04-29 13:00') ---- select hour as hourly_time,median_price from dex.prices where contract_address =0x6982508145454ce325ddbe47a25d4ec3d2311933),hourly_token_price_detail as (selecttime as hourly_time,first_value(median_price)over(partition by flag order by time) as median_pricefrom(selectt2.time,t1.median_price,sum(if(t1.median_price is null,0,1)) over(order by t2.time) as flagfrom hourly_token_price_detail_without_seq t1RIGHT JOIN time_seq t2 on t1.hourly_time=t2.time