price AS (

select

Token_address,

Price,

rank() over (

partition by

Token_address

order by

HOUR desc

) AS rank

from

avalanche.core.fact_hourly_token_prices

qualify

rank = 1

),

tb AS (

select

BLOCK_TIMESTAMP,

TX_HASH,

'Add' AS Action,

origin_from_address AS user,

Pool_name AS pool,

TOKENS:token0 AS Token0,

SYMBOLS:token0 AS Symbol0,

DECODED_LOG:amount0 / power(10, A.DECIMALS:token0) AS amount0,

amount0 * B.price AS amount0_USD,

TOKENS:token1 AS Token1,

SYMBOLS:token1 AS Symbol1,

DECODED_LOG:amount1 / power(10, A.DECIMALS:token1) AS amount1,

amount1 * C.price AS amount1_USD

from

avalanche.core.ez_decoded_event_logs