select date_trunc(day,BLOCK_HOUR) as date, 'algorand' as chain, avg(PRICE_USD) as token_price

from algorand.defi.ez_price_pool_balances

where BLOCK_HOUR::date>=CURRENT_DATE-{{Days_Before}} and ASSET_ID=0

group by 1,2

union all

select date_trunc(day,HOUR) as date, 'ethereum' as chain, avg(PRICE ) as token_price

from ethereum.core.fact_hourly_token_prices

where TOKEN_ADDRESS is null and HOUR::date>=CURRENT_DATE-{{Days_Before}}

group by 1,2

union all

select date_trunc(day,HOUR) as date, 'optimism' as chain, avg(PRICE ) as token_price

from optimism.core.fact_hourly_token_prices

where TOKEN_ADDRESS='0x4200000000000000000000000000000000000042' and HOUR::date>=CURRENT_DATE-{{Days_Before}}

group by 1,2

union all

select date_trunc(day,RECORDED_HOUR) as date, 'solana' as chain, avg(CLOSE) as token_price

from solana.core.fact_token_prices_hourly

where RECORDED_HOUR>=CURRENT_DATE-{{Days_Before}} and SYMBOL='SOL'

group by 1,2

union all

select date_trunc(day,TIMESTAMP) as date, 'near' as chain, avg(PRICE_USD) as token_price

from near.core.fact_prices

where symbol='wNEAR' and TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}}

group by 1,2

union all

select date_trunc(day,TIMESTAMP) as date, 'flow' as chain, avg(PRICE_USD) as token_price

from flow.core.fact_prices

where TOKEN_CONTRACT='A.1654653399040a61.FlowToken' and TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}}

group by 1,2

),

near_TX AS ( -- credit to Pinehearst#1947

BLOCK_TIMESTAMP,