token_bonk 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))

,raw_data_naer 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 raw_data_naer

group by 1,2

,price_calc_near as

(select

"date",

COALESCE(

price,

LAG(price, 1) OVER (ORDER BY BLOCK_TIMESTAMP::date),

LEAD(price, 1) OVER (ORDER BY BLOCK_TIMESTAMP::date),