WITH token_addresses AS (

select * from

( values

( '6', 'USDC.e', 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near'),

( '24', 'wNEAR', 'wrap.near'),

( '18', 'KAT', 'kat.token0.near')

)as a (decimal, ADDRESS_NAME, address)

),

near_swap_data AS (

SELECT

BLOCK_TIMESTAMP,

AMOUNT_OUT_RAW/pow(10,24) as AMOUNT_OUT ,

AMOUNT_IN_RAW/pow(10,6) as AMOUNT_IN

from near.defi.fact_dex_swaps

WHERE SWAP_INPUT_DATA:pool_id ='3'

AND TOKEN_IN='a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near'

AND TOKEN_OUT='wrap.near'

),

price_near AS (

SELECT

date_trunc('hour',BLOCK_TIMESTAMP) AS "date",

BLOCK_TIMESTAMP,

avg(AMOUNT_IN/AMOUNT_OUT) AS price

FROM near_swap_data

group by 1,2

),

price_calc_near as

(select

"date",

COALESCE(

price,