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

avalanche.core.ez_decoded_event_logs

inner join avalanche.defi.dim_dex_liquidity_pools A on pool_address=contract_address

inner join avalanche.core.fact_hourly_token_prices B on B.token_address=TOKENS:token0 and Date_trunc('Hour',BLOCK_TIMESTAMP)=B.Hour

inner join avalanche.core.fact_hourly_token_prices C on C.token_address=TOKENS:token1 and Date_trunc('Hour',BLOCK_TIMESTAMP)=C.Hour

ORIGIN_FUNCTION_SIGNATURE IN ('0x88316456','0x219f5d17','0xac9650d8') and ORIGIN_TO_ADDRESS='0x655c406ebfa14ee2006250925e54ec43ad184f8b'

and EVENT_NAME='Mint'

union ALL

BLOCK_TIMESTAMP,

TX_HASH,

'remove' AS Action,

origin_from_address AS user,

Pool_name AS pool,